WOS (Weeks of Supply) DAX Measure

paddyg95

New Member
Joined
Apr 19, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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


Product CodeProduct CategoryWeekStock on HandForecastGoods InSOH - Forecast + Goods inRunning Inventory TotalWeeks Cover
COS-BE-UK-4912Beans13/04/202017793194315850158507.71
COS-BE-UK-4912Beans20/04/202019437696575321603
COS-BE-UK-4912Beans27/04/20203152-315218451
COS-BE-UK-4192Beans04/05/20203158-315815293
COS-BE-UK-4912Beans11/05/20203158-315812135
COS-BE-UK-4912Beans18/05/2020819-81911316
COS-BE-UK-4912Beans25/05/2020819-81910497
COS-BE-UK-4912Beans01/06/20201015-10159482
COS-BE-UK-4912Beans08/06/20202515-25156967
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,114,587
Messages
5,548,889
Members
410,883
Latest member
pinch
Top