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.
Hi @marieke, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
i tried to work some things out based on the topic you advised me and I feel I’m a little bit closer now, but still can’t get it working as it should
I have now added a copy of the data model and the report and hope you will be able to help me out.
The stock level for last week is monitored in our system and will be updated every week. The last known value of this stocklevel, needs to be the starting point for future calculations. In this calculations the expected sales forecast, and the planning of incoming goods will affect the stocklevel.
So now in week 202040 we started with a stocklevel of 3694. Expected forecast is -53 to sell and there are no goods coming in this week. So expected outcome at the end of this week should be 3694 - 53 so 3641 and not 4031 as it is telling me now.
And then for week 202041, 3641 should be the starting point, and then forecast of -38 to sell and 800 goods to come in, so a total change of 762. So at the end of week 202041, I expected to see 2879.
I hope to fill in and try to find solution when @Melissa is busy. I quickly tried to create logic and it seems that it is working :). Please find below solution.
Stock Projection-2 =
VAR LastStockDay =
CALCULATE (
MAX ( 'Kalender'[JJJJWW] ),
FILTER (
ALLSELECTED ( 'Kalender'[JJJJWW] ),
ISBLANK ( [QTY Stock last week] ) = FALSE ()
)
)
VAR ExistingStocks = [QTY Stock last week] + [Stock Change]
VAR ExistingStocksLastWeek =
CALCULATE ( [QTY Stock last week], 'Kalender'[JJJJWW] = LastStockDay )
+ CALCULATE ( [Stock Change], 'Kalender'[JJJJWW] = LastStockDay )
VAR StockChangeNew =
CALCULATE (
[Stock Change],
FILTER (
ALLSELECTED ( 'Kalender'[JJJJWW] ),
'Kalender'[JJJJWW] > LastStockDay
&& 'Kalender'[JJJJWW] <= MAX ( 'Kalender'[JJJJWW] )
)
)
VAR FinalMeasure =
IF (
ISBLANK ( [QTY Stock last week] ) = TRUE (),
ExistingStocksLastWeek + StockChangeNew,
ExistingStocks
)
RETURN
FinalMeasure
can I please ask for your help again with this report? It looked like everything was fine after your help, but now new shipments are on it’s way and I’m facing another issue. I tried several calculations yet, but can’t find the solution…
One order (no. 825) is now partly shipped. The total order is 27.503 SKU, but only 19.755 are shipped now and they will arrive on the 4th of November (we want to use arrival date of a container once a container is planned). So, 7.748 are not shipped yet, and for this we want to use the planned date in the order, so in this case 30-11-20.
Looking at the table in “Check Shipped and Planned” everything seems to be fine.
But then I want to use these figures in a weekly planning as on page “forecast berekening”, and there it goes wrong… In week 45 the shipped 19755 will arrive, but the calculation here also gives me 19.755 as not shipped yet. So in total, it will give me a calculation of 0. And then in week 202049 we expect only the last 7748 SKU to arrive, but then it is giving me the total number for the complete order…
Want i want to see: in week 45 arrival of the 19.755 goods shipped yet, and in week 49 arrival of the planned 7.748…
Can you please help me once again as this report is driving me crazy now
Your ‘Checked Shipped and planned table’ is working because you are not using anything from you Calendar table. In highlighted figure above, you have [QTY shipped container] as 19755 as you have relation and in this week you have some shipment. But your other measure [QTY not shipped yet] = [QTY Open] - [QTY Container Shipped] is dependent of [QTY Open] and [QTY open Measure] is below:
QTY Open = SUM(‘WH Container Item (Open orders)’[qty_open])
Above table 'WH Container Item (Open orders) has all dates for the end of the month, so it does not match with week 202045 and gives 0 and vice versa.
Now, in order to resolve this issue, I have created 2 measures which will work within this context.
We might need to do some work in the total if it is required. Please feel free to get in touch in case of any confusion as it took me some time to understand data model and your calculations
Many thanks for your help again, I will look at it into more detail later this week and let you know if it helped me out, but I think it certainly will do
Thank you!
I week 45 the formula for the total stock level (voorraadniveau) is working fine, but for week 49 it doesn’t calculate the incoming goods, while the formulas are the same for every week…
In the meantime I’m still trying to sort this out, but I can’t get in working properly unfortunatelly.
But, now I have presented the report it in a different lay-out, as I was requested to do so by my manager. It will give him a more detailed overview of all products and their stock levels. I have only changed the lay-out, not the formulas. So the problem still exists.
But the strange thing is, I have found out that is works correctly on individually product lines.
As for example, looking at product 64428NP, Voorraad (= Stock Level) for week 20248 was 5773. Then in week 202049 57 will be sold and 1750 will arrive, so a change of 1693. That will make a stock level of 5773 + 1693 = 7466 and that is exactly what the report is displaying.
For the other product in this printscreen, goods already arrived earlier in week 202045 (where the complete calculation is working fine) so stock level will only change with -61 sold.
Looking at the total top rows, the individually rows are all correct, but in the last total row, it should give the total stocklevel of 7466 Plus 2498, (9964 )but in only shows 8214 instead
It really would be great if you would have some time to look at it and hopefully can figure out what the problem is…
Thanks for your detailed description. Actually it was not working because in total, we did not had any filter context. Now, I created filter context that you used in the the table to calculate total and it seems working. Please try to create it and let me know if it does not work.
It looks fine now, but just for my own understanding, the previous method already seemed to work in week 45, but not in week 49. If this only has to do with the total calculation, then why was it already working in week 45?
That’s great that it is working for you. To be honest, I am not sure about it as it could be due to some intermediate calculations as we are using different measures to come up with the total or it could be issue of some model relationship.
But you are welcome to come back if you feel any issue with calculations as I can surely miss certain things as I don’t understand the language used(may be Dutch/Europen)
me again
I thought the calculations were fine and working properly, but after having a good look at it, I see there are still some issues. I have now tried to write the relevant calculations all in English (indeed my language is Dutch .
I have also attached an Excel sheet now with the calculation as how I want it to work. The difficult thing is that I want to work from the most recent stock value know from the database, and from that point on, it should be calculated. So now, we know the stocklevel for this week is 198.333, but this might change for next week as the forecast might be different from the real sales amounts.
I hope you would be so kind to have one more look at it?
.