Forum Discussion
Inventory Management
Hi RNair ,
Sorry for the late reply, yes you are correct recursive calculation cannot be done in DAX, you can simulate it but no exaclty a recursive.
You can do it in Query editor using a formula.
https://www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/
1. Column K is just multiplying Column J x Column D for the rows marked "Sell" in Column C. Column L is just addition of Column F and Column K. So yes the ultimate objective is Column L but my difficulty is in calculating Columns I and J.
2. For sell transactions, the unit cost after transaction is the same as before transaction. That means where Column C= Sell; Column J= Column I. For buy transactions, ie where Column C= "buy", Unit Cost Basis after transaction = ( Unit Cost before transaction x Qty before transaction + Cost of transaction )/ Qty after transaction. ie where Column C = "Buy", (Column I x Column G + Column D)/ Column H. For the row 6; ( 2.067 x 75 + 468) / 315 = 1.978.
3. For each row Column I = Column J on the previous row.
Thanks again for the interest.
I see what you mean. That's going to be quite tough. There's recursion there and DAX doesn't lend itself well to that. I'll have a closer look later but I doubt it can be done in the way you propose.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AIB,
Thank you the response and the advice.
I am a little bit disappointed such an incredible software has got an inherent weakness like this.
Have spent almost a couple of months studying DAX now, and am really impressed with its abilities but this has rocked my faith a bit.
Please spend a little time when able to look at this and advise if any ideas possible.Thank you again and good night.
Regards,
RNair
Let's see if any of these knowledgeable people have ideas:
Zubair_Muhammad , mahoneypat , MFelix , MattAllington , Greg_Deckler GilbertQ