Forum Discussion
Inventory Forecast Recursive Calculation
Hi Ashish, thank you so much.
What if I have a new inventory value in February, and I'm supposed to take that value for the DATESYTD calculation? Do I use the LASTNONBLANK function?
Hi,
I think my solution should still work. Please try it.
Hi Ashish, no it doesn't, if I have another value for SKU A in February 2018 (say 400), your measure totals up the inventory values for the past two months. which means the remaining inventory for February will become 900 - (28 + 22) = 850.
The february inventory data should override the previous inventory data and the calculation should be carried forward. I.e. the february forecast value should be 400-22 = 378, and march forecast should be 378 - 16 = 362 etc etc.
(I'm trying to use :
CALCULATE ( SUM ( Inventory[Qty] ) , LASTNONBLANK(...))
but I am still stumbling (I'm still quite new at DAX)).
Thank you!
Hi,
I cannot quite appreciate your example. First, there should also be Purchases data. so that opening stock + Purchases - Sales = Clsoing stock. In your example, the inv qty for Feb should be 472-22=450.
Am i incorrect?
I fully understand.
1. On purchasing data: I am excluding it for the sake of simplicity. And not all products are bought every month.
2. On using 400 instead of 472: The reason why I used "400" (note that this is a manual input) is because there will be stock takes at the end of every month, and sometimes there will be discrepancies in the inventory. So my team decided that we should have a manual input of inventory at the end of each month.
So the objective is to take the latest inventory data available and then perform further forecasting, month-on-month.