Power Query FIFO Inventory Valuation - recursive?

[ruhrbi]

Hi all!

We had experienced a problem with our inventory valuation in DAX, as we got a circular dependency. As DAX is not capable of recursion, we are pretty sure to solve this problem with power query functions.

Problem is the following:

Assuming i start with an initial stock: QTY 900000, Price 0,3 and I also have my monthly Sales QTY and Purchase QTY and price I could create the following (the Excel with the formulas for 2 months is attached)

Ticket Performance
Monthly Average FIFO Calculation	Price	Qty	Value
Total Opening	0,30	900.000,00	270.000,00
Total Purchases	0,88	1.980,00	1.749,40
Total Opening + Purchases	0,30	901.980,00	271.749,40
Inventory From Opening	0,30	899.914,40	269.974,32
Inventory From Purchases	0,88	1.980,00	1.749,40
Closing Inventory	0,30	901.894,40	271.723,72
COGS From Opening	0,30	85,60	25,68
COGS From Purchases	0,88	-	-
Total COGS	0,30	85,60	25,68

Then, in the next month I would "just" need to take my closing inventory row as my opening row.

This is where the problem sits as it would need to reference itself while defining the formula like so:

openingunitrate=

if "initialstockunitrate"=blank() then
    inventoryfromopening(monthyear-1)*openingunitrate(monthyear-1)+
    inventoryfrompurchases(monthyear-1)*purchaseunitrate(monthyear-1)
/
    closingstock(monthyear-1)

else

     "initialstockunitrate"

Also what would need to be done is a grouping per "Category" and the column compliance_year. It should start in the next year with 0 as we only have an initial stock this year.

I don't know if this will actually require a recursive function in PowerQuery, so if you have any other idea how to solve it, it's fine as well! In my head this logic totally makes sense but as I have little experince with PQ I'm struggling to actually code it there.

I attached an Excel file that includes both the calculation done in Excel and the sample data which would need to be enhanced.

Please find it under the following link:

SAMPLE DATA 

thanks and regards,

Martin

[ruhrbi]

So I've now created a GroupedIndex which should work in order to loop via List.Generate or List.Accumulate.

To make it clearer what I need, I attached also a recent screenshot of the data. It should be straightforward for the pros...

OpeningUnitRate is actually the column i need to calculate, it's just for better reading that it is already here in this case. It resembles the initial unit rate (which I got for some months).
I need:

UnitRate=
If OpeningUnitRate<>0 then
OpeningUnitRate
else
InventoryFromOpening(GroupIndex-1)**UnitRate*+ (I need the previous result of the column i'm calculating!)
InventoryFromPurchases(GroupIndex-1)*PurchasePrice(GroupIndex-1) (I have the PurchasePrice)
/
ClosingStock(GroupIndex-1) (I have the closingStock)

[v-deddai1-msft]

Hi @ruhrbi ,

Here is a similiar post for your reference: https://community.powerbi.com/t5/Power-Query/Simple-recursive-calculation-Power-Query/m-p/1093141

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[ruhrbi]

Thanks, that helped a lot! Still a problem though, but I think it's easy to fix:

My function:

let
    unitratefunction = (InitialUnitRate,InventoryFromOpening,InventoryFromPurchases,PurchaseUR,ClosingStock,Counter,Index) as number=>

    let

    UR = if InitialUnitRate <> 0 then InitialUnitRate else 
        ((InventoryFromOpening{Counter-1}*@unitratefunction(InitialUnitRate,InventoryFromOpening,InventoryFromPurchases,PurchaseUR,ClosingStock,Counter-1,Index))+
        InventoryFromPurchases{Counter-1}*PurchaseUR)/ClosingStock,
  
    Return = UR

    in

    Return

in
    unitratefunction

 My added column calling the function:

    UR=Table.AddColumn(#"Added Custom12", "UR", each unitratefunction([OpeningUnitRate],[InventoryFromOpening],[InventoryFromPurchases],[PurchasedValueCumu]/[PurchasedTicketsCumu],[ClosingStock],1,[Index]))
in
    UR

I'll then get the following error in those items where initialunitrate is 0:

An error occurred in the ‘unitratefunction’ query. Expression.Error: We cannot convert the value 2993376.231 to type List.
Details:
    Value=2993376,231
    Type=[Type]

Any ideas? I don't want a list, I just want the result as a number

[v-deddai1-msft]

Hi @ruhrbi ,

Best person to help on it is @ImkeF one of the best (if not the best) user in M language in the community. 

Best Regards,

Dedmon Dai

[ruhrbi]

I had looked into that and understood that it resembles a for loop in power query.

That should work in theory but I struggle translating that I to practice, especially the grouping part.

[lbendlin]

This is a cumulative scenario, not a recursive scenario. Recursive functions go to deeper levels of a hierarchy (and back up) while you are staying at the same hierarchy level.  Familiarize yourself with the List.Accumulate function - it is extremely powerful.