Weeks of Supply Measure DAX

[gardas_swathi]

Hi Everyone,

I need to create a Weeks of Supply Measure which is calculated based on current day stock and future weeks forecast.

Below is the sample data from my Analysis Cube. we do have a product Dimension and Date Dimension and these facts are related to them

For productID 1 as of date 03/17 Weeks of Supply should be 2.2

ProductID 1
Stock 03/17 - 137
forecast 3/20/2020  76 => 137-76= 61  =>1
             3/27/2020   41 => 61-41= 21  =>1
              4/3/2020    86  => 21-86 =>  -65 => 0.241245136(21/86For Negative week value take the % of week covered)

Sample Data

Stock Table

DateID	ProductId	Stock
3/1/2020	1	10
3/2/2020	1	137
3/3/2020	1	1
3/4/2020	1	27
3/1/2020	2	117
3/2/2020	2	68
3/3/2020	2	23
3/4/2020	2	33
3/1/2020	3	1
3/2/2020	3	88
3/3/2020	3	105
3/4/2020	3	48
3/9/2020	1	10
3/10/2020	1	137
3/11/2020	1	1
3/12/2020	1	27
3/9/2020	2	117
3/10/2020	2	68
3/11/2020	2	23
3/12/2020	2	33
3/9/2020	3	100
3/10/2020	3	88
3/11/2020	3	105
3/12/2020	3	48
3/16/2020	1	10
3/17/2020	1	137
3/18/2020	1	150
3/19/2020	1	27
3/16/2020	2	117
3/17/2020	2	68
3/18/2020	2	23
3/19/2020	2	33
3/16/2020	3	1
3/17/2020	3	88
3/18/2020	3	105
3/19/2020	3	48

Forecast Table

DateID	ProductID	Forecast
3/6/2020	1	56
3/6/2020	2	100
3/6/2020	3	86
3/13/2020	1	31
3/13/2020	2	46
3/13/2020	3	61
3/20/2020	1	76
3/20/2020	2	91
3/20/2020	3	26
3/27/2020	1	41
3/27/2020	2	56
3/27/2020	3	71
4/3/2020	1	86
4/3/2020	2	101
4/3/2020	3	116

Please let me know if you have any questons if my post is not clear. Many Thanks 

[kentyler]

Do you expect to take into account stock that is added, as well as stock that is expended ?

Is the calculation as of a certain date ? or is it always starting from the same date ?

[gardas_swathi]

HI Kentyler,

I need to take into account Stock as of today and show the Weeks of Supply for Future Weeks

[artemus]

Ok, here it is in 3 measures. This assumes you have a DateTable and a Products table:

Forecast running total in DateID = 
CALCULATE(
	SUM('Forecast'[Forecast]),
	FILTER(
		ALLSELECTED('Forecast'[DateID]),
		ISONORAFTER('Forecast'[DateID], MAX('Forecast'[DateID]), DESC) &&
        [DateID] > MAX(Inventory[DateID])
	)
)

Latest inventory = MAXX(TOPN(1, Inventory, RELATED(DateTable[Date]),DESC), [Stock])

Inventory with Prediction = IF(MAX(DateTable[Date]) < CALCULATE(MAX(Inventory[DateID]), ALL(Inventory[DateID])), MAX(Inventory[Stock]), [Latest inventory] - Forecast[Forecast running total in DateID])

This shows a prediction for every day you don't have an entry in your inventory table.

[Anonymous]

Hi @artemus ,

I tried those 3 measures and I may be missing something but for me the 'inventory with prediction' measure gave the running total for invntory less demand.

I could just be getting something wrong but was the measure not supposed to calculate how many weeks of supply were available after calculating that running total?

Thank you,

Paddy

[Greg_Deckler]

Sorry, just jumping in here, I haven't tried anything but @artemus solution looks promising. I did create a Days of Supply Quick Measure once. May or may not be of use. https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

[Anonymous]

HI @Greg_Deckler 

I tried to create a measure for weeks of supply using the instructions in your post (just removing the *7 parts to do weeks instead of days) but I'm having some trouble. 

I used the same data and created one table called 'InventoryTable' and 3 columns for week, inventory and demand. I created the __week and __inventory measures fine I think, but I run into problems when trying to create the table in:

VAR__table = FILTER(ALL(Inventory),[Week]>__week)

I'm using Excel rather than Power BI so don't know if it's different for that, but I added a blank table to the data model from Excel and then entered: Measure 2:=FILTER(ALL(InventoryTable),InventoryTable[Week]>[__Week])  in the blank table but got the following error message: 'the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

If you have any idea on what I'm doing wrong and could help I would be really grateful!

Many thanks,

Paddy

[gardas_swathi · Accepted solution]

HI Paddy,

This is what worked for me. This is a measure in Analysis Service Tabular Model

"// Latest Forecast Version",
"VAR MAXCDP =",
" MAX ( 'Forecast Version'[LatestForecastVersion] )",
"//Current Friday Date",
"VAR SELECTEDDate =",
"//MAX('Date'[Fiscal Week])",
" (6 - WEEKDAY ( TODAY () )",
" + TODAY ())+7",
"//Inventory Value",
"VAR LatestInventory = 'Inventory'[Stock]",
"//Query to get Consensus Forecast Value from the current week",
"VAR BurnTab =",
" FILTER (",
" SUMMARIZE (",
" 'Demand',",
" 'Date'[Fiscal Week],",
" 'Demand'[CDPForecastRecordDateID],",
" 'Product'[Business Unit Name],",
" \"Consensus Final\", CONVERT (",
" SUM ( 'Demand'[ForecastSell] ),",
" INTEGER",
" )",
" ),",
" 'Demand'[CDPForecastRecordDateID] = MAXCDP",
" && 'Date'[Fiscal Week] >= SELECTEDDate",
" )",
"//Step to Create Running Total Column ",
"VAR BurnTab2 =",
" ADDCOLUMNS (",
" BurnTab,",
" \"Running Total\",",
" VAR CurrentConsenusDate = [Fiscal Week]",
" RETURN",
" SUMX (",
" FILTER ( BurnTab, [Fiscal Week] <= CurrentConsenusDate ),",
" [Consensus Final]",
" )",
" )",
"//ADD Inventory to all rows",
"VAR BurnTab3 =",
" ADDCOLUMNS ( BurnTab2, \"Latest Inventory\", LatestInventory )",
"",
"VAR BurnTab4 =",
" ADDCOLUMNS (",
" BurnTab3,",
" \"Inventory with Prediction\", [Latest Inventory] - [Running Total]",
" )",
"VAR BurnTab5 =",
" ADDCOLUMNS (",
" BurnTab4,",
" \"BurnDown1\", IF ( [Inventory with Prediction] > 0, 1, 0 )",
" )",
"VAR BurnTab6 =",
" ADDCOLUMNS (",
" BurnTab5,",
" \"BurnDown2\", IF (",
" [BurnDown1] = 1,",
" 0,",
" IF (",
" [Inventory with Prediction] < 0,",
" IF (",
" DIVIDE (",
" [Latest Inventory] - ( [Running Total] - [Consensus Final] ),",
" [Consensus Final]",
" ) < 0,",
" 0,",
" DIVIDE (",
" [Latest Inventory] - ( [Running Total] - [Consensus Final] ),",
" [Consensus Final]",
" )",
" )",
" )",
" )",
" )",
"VAR BurnTab7 =",
" ADDCOLUMNS ( BurnTab6, \"FinalBurnDown\", [BurnDown1] + [BurnDown2] )",
"",
"VAR TotalConsensus =SUMX(BurnTab7,[Consensus Final])",
"",
"VAR BurnDownFinal = SUMX ( BurnTab7, [FinalBurnDown] )",
"",
"VAR FinalValue = IF(TotalConsensus=0 && LatestInventory<>0,9999,IF(TotalConsensus<>0 && LatestInventory=0,0,BurnDownFinal))",
"RETURN",
" FinalValue",
""

[Anonymous]

Hi @gardas_swathi 

Thank you for your reply but I'm not currently using Analysis Service Tabular Model and was hoping to just use a DAX measure.

My data model is structured as below which I've filled with just one product. I've merged a couple of tables together in the data model for now just while I'm testing.

The first table is what is seen in the 'stock in hand' column, which is just a weekly report from our warehouse which states the quantity in stock at the start of that week. The following weeks are blank on this column as it just looks at the present position this week.

This is then combined with our forecast sales out and goods in for the following weeks. The rank is just the week number. The 'inventory' column is then a cumulative total of (current stock in hand + goods in - forecast sales), which is why inventory is 3000 in week 13/04 because at the start of the week stock in hand is 5000 and forecast sales are 2000 for that week. In the second week you've got the 3000 in inventory from last week, less 2000 sales and plus 1000 goods in, so 2000 in inventory.

I then want to use this cumulative total column to just tell me how many weeks before this goes into negative forecast stock, as shown in the 'weeks cover' column. Do you know of a DAX measure which might achieve this?

No worries if not. Many thanks for your help.

Paddy

Product Code	Product Category	Date	Stock in Hand	Forecast Sales	Goods In	Rank	Inventory	Weeks Cover
COS/BE/UK-4912	Beans	13/04/2020	5000	2000		1	3000	2.5
COS/BE/UK/4912	Beans	20/04/2020		2000	1000	2	2000	1.66
COS/BE/UK/4912	Beans	27/04/2020		3000		3	-1000	0