illusionek
Board Regular
- Joined
- Jun 21, 2014
- Messages
- 104
I am looking for a solution to calculate projected stock coverage using either DAX or M language, whichever is easier. I googled and there are quite few solutions out there but they either dont work or use different assumptions and I cant make them work for me. This challenge exceeds my skills/knowledge at the moment, hence I am looking for some support.
Ultimately I have a very simple table with multiple products and data is split by week commencing. My data table has my current/projected stock position and also sales forecast. The assumption is that I compare stock in particular week with forward looking forecast to see how long the stock will last for example, Product A w/c 20th Jun has 468.3 in stock and based on forward forecast (starting from w/c 27/06 onwards) that would last me 23.6 days then w/c 27th Jun, I expect to have 437.6 in stock and based on the forecast from w/c 04/07 onwards that would last me 22.1 days etc. The time horizon is dynamic, so this would need work for 10weeks or 25weeks etc. Also if stock exceeds sales forecast in total horizon then I would like to get value 9999.
Ultimately I have a very simple table with multiple products and data is split by week commencing. My data table has my current/projected stock position and also sales forecast. The assumption is that I compare stock in particular week with forward looking forecast to see how long the stock will last for example, Product A w/c 20th Jun has 468.3 in stock and based on forward forecast (starting from w/c 27/06 onwards) that would last me 23.6 days then w/c 27th Jun, I expect to have 437.6 in stock and based on the forecast from w/c 04/07 onwards that would last me 22.1 days etc. The time horizon is dynamic, so this would need work for 10weeks or 25weeks etc. Also if stock exceeds sales forecast in total horizon then I would like to get value 9999.
| Product | W/C | Projected Stock Cover | Sales Forecast | Stock |
| Product A | 20/06/2022 | 23.6 | 167.4 | 468.3 |
27/06/2022 | 22.1 | 138.7 | 437.6 | |
04/07/2022 | 5.0 | 138.7 | 100.0 | |
11/07/2022 | 2.6 | 138.7 | 51.3 | |
18/07/2022 | 0.0 | 138.7 | 0.0 | |
25/07/2022 | 10.5 | 138.7 | 200.0 | |
01/08/2022 | 25.8 | 133.8 | 500.0 | |
08/08/2022 | 29.4 | 133.8 | 582.2 | |
| Product B | 20/06/2022 | 84.0 | 2.1 | 49.7 |
27/06/2022 | 84.0 | 0.4 | 49.3 | |
04/07/2022 | 81.8 | 0.4 | 48.9 | |
11/07/2022 | 74.8 | 0.4 | 48.5 | |
18/07/2022 | 67.8 | 0.4 | 48.1 | |
25/07/2022 | 60.8 | 0.4 | 47.7 | |
01/08/2022 | 84.0 | 0.5 | 75.2 | |
08/08/2022 | 80.0 | 0.5 | 74.7 |
