Inventory conversion -Is this possible?-

[luirib]

I have to translate the inventory from Euros to US dollars.
My fact table is in Euros
The report I created in Euros is very straight forward:
Opening Balance: Ending balance pushed back one month (DATEADD)
Purchases: all debits to the account
Consumption: all credit to the account
Ending Balance: Add all debits minus all credits life to date

To convert the Purchases to US dollars I use the average exchange rate of the month. Very easy.
The problem starts when I try to convert the Consumption. To do so, what I am trying to use is a blended rate of the opening balances. In other words, I am trying to do the following:
Consumption in Euros / (Opening Balance Euros / Opening Balance US dollars)

No matter how I try this, I end up getting a circular reference since I have to put on the Consumption in US dollars measure its very measure one month ago (i.e ending balance of prior month).

This same inventory conversion can be done in regular Excel very easily, but I think that given the logic of PowerPivot, it cannot be done using DAX.

Please let me know if mine is a worthless pursuit. Thank you.

 

[Matt Allington]

Hard, but not hopeless. I obviously haven't seen your measures, but it seems to me that you can create a table (table1) (probably with filter but maybe calculatetable) that holds the input consumption data used to calc the opening balance of stock? Are you saying you then want to blend this stock value with the credits to the account to get a blended avg value? I assume you must have the conversions in a data table that varies by month, right? So the credits can be filtered in another table (table 2). Then T1 * rate for that period + T2 * rate for that period / qty

maybe I am missing some detail - not sure.

 

[ImkeF]

Maybe it would be less pain to do it in Power Query instead: Translate your "source"-data for your inventory in PQ before loading to PP (as an additional column)- then it would be just the same as your EUR measures, just a different reference field.

 

[luirib]

Maybe it would be less pain to do it in Power Query instead: Translate your "source"-data for your inventory in PQ before loading to PP (as an additional column)- then it would be just the same as your EUR measures, just a different reference field.

Let me give you an example with 3 months to illustrate the issue:

Month Op Bal Eu Purc Eu Consump Eu Closing Eu Op Bal USD Purc USD (*) Consump USD (**) Closing USD
Jan 1,000 200 -300 900 1,100 208 -330 978
Feb 900 400 -100 1,200 978 422 -92 1,308
Mar 1,200 600 -400 1,400 1,308 653 -367 1,594

(*) I convert these at the monthly average rate.
(**) This is the one that I cannot resolve. In this case and for Jan I would get the blended rate of the opening balance (1,000/1,100)=0.909 and then I would convert the Consump in Eu using this blended rate: -300/0.909. For Feb the rate would be 900/978=0.920. For Mar it would be 1,200/1,308.

I have a separate table with the daily exchange rates. The blended rate cannot be calculated in this table since it is inventory transaction dependent.

Thanks for your input.

 

[ImkeF]

So far I haven't seen anything that can be done in Excel that could not be done in Power Query. If you could provide an example of your Excel-solution I would be able to tell you how to translate it into PQ.

 

[luirib]

Imke, this is a basic example of what works in Excel but I cannot figure out how to do in DAX. The consumption in USD is what I cannot figure out how to do (I added the formulas that I use in Excel). Thanks for your help.

A1 Month
A2 Jan
A3 Feb
B1 Opening Euros
B2 $1,000.00
B3 $1,200.00
C1 Purchases Euros
C2 $200.00
C3 $300.00
D1 Comsumption Euros
D2 $(100.00)
D3 $(200.00)
E1 Closing Balance Euros
E2 $1,100.00
E3 $1,200.00
F1 Opening USD
F2 $1,110.00
F3 $1,217.00
G1 Purchases USD
G2 $218.00
G3 $326.00
H1 Consumption USD
H2 $(111.00) =D2/(B2/F2)
H3 $(202.83) =D3/(B3/F3)
I1 Closing Balance USD
I2 $1,217.00
I3 $1,340.17

 

[ImkeF]

Shouldn't there be formulas in either F or I as well?

 

[luirib]

Correct, Imke. I only included the formula that is giving me an issue, but every single cell is formulated.

 

[ImkeF]

got it - but: how do you calculate the 1st consumption with a opening Balance of 0?

 

[luirib]

I have opening balances both for Euros and US dollars that come from our previous system. These I plug into my model, so I do not start from a zero balance.