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.

 

[ImkeF]

this is recursive calculation which Excel handles easily, Power Pivot almost not at all, and Power Query with some advanced concepts. This might be a bit too heavy for a beginner, but if you are interested - here it goes:

let
    Source = Excel.CurrentWorkbook(){[Name="StockEUR"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Opening EUR", Int64.Type}, {"Purch EUR", Int64.Type}, {"Cons EUR", Int64.Type}, {"ClosBal", Int64.Type}}),
    AddInd = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),

    MergeXrate = Table.NestedJoin(AddInd,{"Month"},AvgXRate,{"Month"},"NewColumn",JoinKind.LeftOuter),
    ShowXrate = Table.ExpandTableColumn(MergeXrate, "NewColumn", {"AvgXrate"}, {"AvgXrate"}),
    Table = Table.AddColumn(ShowXrate, "PurchUSD", each [Purch EUR]*[AvgXrate]),
    
fnOpUSD = (OpUSDStart) =>
let

ListPurchUSD=List.Buffer(Table[PurchUSD]),
ListConsEUR=List.Buffer(Table[Cons EUR]),
ListOpEUR=List.Buffer(Table[Opening EUR]),

Iterate = List.Generate(
()=>[Counter=0, OpUSD=OpUSDStart],
each [Counter]<=List.Count(ListPurchUSD),
each [Counter=[Counter]+1,
OpUSD=([OpUSD]+ListPurchUSD{[Counter]}+(ListConsEUR{[Counter]}/(ListOpEUR{[Counter]}/[OpUSD])))],
each [OpUSD]),

Table_ = Table.Buffer(Table.FromColumns({{1..List.Count(ListPurchUSD)}, Iterate}))

in
Table_,

    #"Invoked FunctionfnOpUSD" = fnOpUSD(OpUSDStart[Column1]{0}),
    Merge = Table.NestedJoin(Table,{"Index"},#"Invoked FunctionfnOpUSD",{"Column1"},"NewColumn",JoinKind.FullOuter),
    OpUSD = Table.ExpandTableColumn(Merge, "NewColumn", {"Column2"}, {"OpUSD"}),
    RemoveIndex = Table.RemoveColumns(OpUSD,{"Index"}),
    #"Added Index" = Table.AddIndexColumn(RemoveIndex, "Index", 1, 1),
    ConsUSD = Table.AddColumn(#"Added Index", "ConsUSD", each #"Added Index"[OpUSD]{[Index]}-[OpUSD]-[PurchUSD]),
    ClosUSD = Table.AddColumn(ConsUSD, "ClosUSD", each [OpUSD]+[PurchUSD]+[ConsUSD]),
    #"Removed Errors" = Table.RemoveRowsWithErrors(ClosUSD, {"ClosUSD"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Errors",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Month", "Opening EUR", "Purch EUR", "Cons EUR", "ClosBal", "AvgXrate", "OpUSD", "PurchUSD", "ConsUSD", "ClosUSD"})
in
    #"Reordered Columns"

here is the link to the file: https://www.dropbox.com/s/au6r35yiaj6c7c2/ME_InventoryRecursive.xlsx?dl=0

How to do this recursion using List.Generate is explained here: Using List.Generate() To Make Multiple Replacements Of Words In Text In Power Query – Chris Webb's BI Blog

 

[luirib]

Thanks Imke. This is, not doubt, very advanced but it is good to know there is a way.

 

[luirib]

Just to close the issue and in case somebody bumps into this post with similar problem, what I ended up doing (albeit not the best from a purist standpoint), I created a tab in Excel that has the report in Euros and, to the right of this report I created a table that converts to US dollars using regular Excel formulas.. This table I connect to my model and off I go. Again, not perfect (e.g. I need to make sure the report gets updated so the table is also updated) but it is pretty straight forward.