Forum Discussion
Inventory Management
Trying to manage inventory and monitor value of inventory.
You build inventory by buying products. When you sell the inventory value drops.
If you buy 100 units of a product at 200 dollars , say, then the cost basis is 200 dollars. Each item in inventory is valued at 2 dollars.
If you further buy 200 units of the same product at 420 dollars, you now have a total of 300 units at total cost of 620 dollars so each item in inventory is valued at 620/300 =2.067 dollars.
If you sell now, and drop inventory, the drop in value is estimated at 2.067 per unit , which is the average cost of retained inventory.
In following table, columns A through E are raw data. Columns F G H are simple calculations which are relatively easy.
Column I and Column J are tricky and keep producing circular reference error.
Need help in replicating Columns I and J in PowerBi using measures.
Regards,
RNair
|
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/
14 Replies
Hi RNair
By requesting a measure you mean you will use that measure in a table visual with columns A to E in the rows??
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
Yes will be displaying these as tables and other visuals also. The objective is to get to column L. I am creating an interface where products and dates are being filtered by slicers so cannot use calculated columns and tables.
1. 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
Hi RNair ,
I was looking at your data and only getting issue in understanding the calculation when you buy values if I look at the information you place how do you calculate the values for the lines below the two values in BOLD (they are the same values) the cost basis for sells then I assume comes from the previous columns quantity * Unit cost after transaction.
RNair wrote:
Date Product Type Qty Transaction Value Cost Basis for "Buys" Qty before transaction Qty After transaction Unit cost Basis before transaction Unit Cost Basis afer transaction Cost Basis for "Sells" Cost Basis 1/01/2020 Shoe Buy 100 200 200.0000 100 2.000 200.0000 16/01/2020 Shoe Buy 200 420 420.0000 100 300 2.000 2.067 420.0000 31/01/2020 Shoe Sell -150 300 150 2.067 2.067 -310.0000 -310.0000 15/02/2020 Shoe Sell -75 150 75 2.067 2.067 -155.0000 -155.0000 1/03/2020 Shoe Buy 240 468 468.0000 75 315 2.067 1.978 468.0000 16/03/2020 Shoe Buy 250 512.5 512.5000 315 565 1.978 2.010 512.5000 This type of calculations you need to use a filtering based on the current row date and then make cumulatives so if you pick up the correct values for each cumulative it should all come together.
If you can share those two calculations I can give you the needed measures.
Can you share the calculation in EXCEL type formula I believe I was abble to replicate but not really sure if my tough is correct.
https://1drv.ms/x/s!Aqc2e7y3QZN5mBgYTvraj_c6q_k4?e=ZC04NP
Felix,
Above spreadsheet Sheet1 contains the calculations.
Thank you for looking at my issue.
Regards,
Ramesan
Am I correct in saying that because this is a recursive calculation it cannot be done in DAX?.
Please advise.
Regards,
Ramesan
