WOS (Weeks of Supply) DAX Measure

New Member
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.

 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

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)