Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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",
""
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 ?
Help when you know. Ask when you don't!
HI Kentyler,
I need to take into account Stock as of today and show the Weeks of Supply for Future Weeks
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.
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
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
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
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",
""
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 | ||
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |