Forum Discussion
Helper IInventory 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/
Community Champion1. The objective is column L or columns I and J as you stated earlier?
2. Can you elaborate on how you get to the 1.978 for "Unit cost Basis before transaction" on the sixth row of the table you show?
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
Helper I1. 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.
- AlB
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
- RNair
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
- AlB
Let's see if any of these knowledgeable people have ideas:
Zubair_Muhammad , mahoneypat , MFelix , MattAllington , Greg_Deckler GilbertQ