I’m currently working on a forecast for stock levels, based on actual stocklevel, minus sales forecast, plus goods that are planned to arrive. But there needs to be done some calculations and I’m really getting stuck in it now…
Actual stock levels are known per week out of the management system from our company.
There is a sales forecast for every week of the year. We also know when new goods will arrive at the warehouse. So I need to calculate current stock level for this week, minus sales forecast for this week, plus incoming goods.
But the problems is that for future weeks, as in the example from week 202040 onwards, I don’t know the stocklevels yet. This needs to be calculated from the last know actual figure, but i really don’t know how to arrange this.
Please find attached an example of what I have made so far. So stock level for week 2020-39 is based on the last know QTY stock for week 38 (3854) - Sales (38) + incoming goods (2780). that makes a total of 6595. And this total at the end of week 39, should be the start amount for week 40.
This is how i have calculated now so far:
Stock Level = [QTY Stock last week] - [QTY Forecast] + [QTY Container] + [QTY Not Shipped] + [QTY Open Pur]
QTY Stock last week = CALCULATE([QTY Stock end of week week], DATEADD(Kalender[Datum],-7,DAY))
QTY Stock end of week week = CALCULATE(SUM(‘Stockmovement history’[qty_free] ), LASTDATE(Kalender[Datum]))
Perhaps I should use the StockLevel as starting value for the next row? But I tried several ways and all of them are not working, so I hope somebody can give me some advice for this.
Many thanks for your help!
Kind regards, Marieke