Hi everyone.
I was hoping someone might have a DAX measure that could be used for calculating weeks stock cover. I have data which combines the current stockholding at the start of the week with the forecast for this week and the following 52 weeks. It doesn't contain historical data, so the minimum week is always the start of the current week. There are columns for current stockholding, the sales forecast and goods receipts. There are then calculated columns for the sum of these 3 movements (so just stockholding - sales forecast + goods receipts) and then a running inventory total of these 3 which shows the forecast stock position over the coming weeks. The weeks cover calculation should then be calculated by deducting forecast sales orders from this running total until it runs down to 0. E.g. on week of 13/04/2020 there is 15850 in the running total, which covers the forecast sales of 1943,1943,3152,3158,3158,819,819 and 1015, before there is 1786 in stock left for the forecast sales orders of 2515, which then covers approximately 0.71 of this week, meaning the weeks cover is 7.71.
I haven't filled in the remaining calculations for weeks cover but it is the same logic of (running inventory total - forecast) until running inventory total is at 0.
Does anyone know I could calculate this within Power Pivot using DAX? I am just using Excel not Power BI so I don't have access to Quick Measures so need to write the DAX but am struggling.
Many thanks in advance,
Paddy
I was hoping someone might have a DAX measure that could be used for calculating weeks stock cover. I have data which combines the current stockholding at the start of the week with the forecast for this week and the following 52 weeks. It doesn't contain historical data, so the minimum week is always the start of the current week. There are columns for current stockholding, the sales forecast and goods receipts. There are then calculated columns for the sum of these 3 movements (so just stockholding - sales forecast + goods receipts) and then a running inventory total of these 3 which shows the forecast stock position over the coming weeks. The weeks cover calculation should then be calculated by deducting forecast sales orders from this running total until it runs down to 0. E.g. on week of 13/04/2020 there is 15850 in the running total, which covers the forecast sales of 1943,1943,3152,3158,3158,819,819 and 1015, before there is 1786 in stock left for the forecast sales orders of 2515, which then covers approximately 0.71 of this week, meaning the weeks cover is 7.71.
I haven't filled in the remaining calculations for weeks cover but it is the same logic of (running inventory total - forecast) until running inventory total is at 0.
Does anyone know I could calculate this within Power Pivot using DAX? I am just using Excel not Power BI so I don't have access to Quick Measures so need to write the DAX but am struggling.
Many thanks in advance,
Paddy
| Product Code | Product Category | Week | Stock on Hand | Forecast | Goods In | SOH - Forecast + Goods in | Running Inventory Total | Weeks Cover | |
| COS-BE-UK-4912 | Beans | 13/04/2020 | 17793 | 1943 | 15850 | 15850 | 7.71 | ||
| COS-BE-UK-4912 | Beans | 20/04/2020 | 1943 | 7696 | 5753 | 21603 | |||
| COS-BE-UK-4912 | Beans | 27/04/2020 | 3152 | -3152 | 18451 | ||||
| COS-BE-UK-4192 | Beans | 04/05/2020 | 3158 | -3158 | 15293 | ||||
| COS-BE-UK-4912 | Beans | 11/05/2020 | 3158 | -3158 | 12135 | ||||
| COS-BE-UK-4912 | Beans | 18/05/2020 | 819 | -819 | 11316 | ||||
| COS-BE-UK-4912 | Beans | 25/05/2020 | 819 | -819 | 10497 | ||||
| COS-BE-UK-4912 | Beans | 01/06/2020 | 1015 | -1015 | 9482 | ||||
| COS-BE-UK-4912 | Beans | 08/06/2020 | 2515 | -2515 | 6967 |
