Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Re: Days of Supply

Greg_Deckler

Super User

Days of Supply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-03-2019
09:29 AM

Suppose you have a weekly forecast of inventory and demand and you wish to know for each week the number of days of supply that you have on hand. That is the purpose of this Quick Measure. Inputs are the current week and inventory as well as the demand column.

Days of Supply = // Get the current week and inventory for the current row VAR __week = MAX([Week]) VAR __inventory = MAX([Ending on hand Inventory]) // Create a table of all weeks greater than the current week VAR __table = FILTER(ALL(Inventory),[Week]>__week) // Add our current inventory from above to each row VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory) // Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand])) // Add the difference in start versus the running total of demand to each row VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand]) // Create a table that only has the positive rows VAR __table4 = FILTER(__table3,[__left]>=0) // With only the positive rows, the MIN is the last row before demand runs out VAR __min = MINX(__table4,[__left]) // Therefore, our base days is the number of rows in this table * 7 VAR __baseDays = COUNTROWS(__table4)*7 // Grab the MAX value of the negative rows, this is the row right after our inventory runs out VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left]) // Divide the row right before the invetory ran out by the sum of the absolute values of right before and after // the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 7 // and this is the number of days in that week before inventory ran out VAR __extraDays = __min / (__min + ABS(__max)) * 7 RETURN __baseDays + __extraDays

Interestingly, this Quick Measure exhibits a form of "looping" in DAX, or at least a work-a-round. Consider that a primary task of this measure is to determin the week in which inventory "runs out". In traditional programming, one would determine this with something like a for or while loop, checking for a boundary condition of the inventory on hand becoming negative with respect to demand. However, in DAX, there are no for or while looping constructs. Thus, instead we create a temporary table where each row in the table represents one pass or iteration through a traditional programming "loop". We can then use our boundary condition to filter down to the specific rows where that boundary condition occurs in order to perform our calculation. As demonstrated in the DAX code above, we can determine the values on either side of our boundary condition as well as how many "interations" were required in order to hit that boundary condition.

eyJrIjoiZDcxY2U3ZjAtM2ZiMy00ZjJhLWE0N2YtZTM5YjFiNDJlMTJlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Proud to be a Datanaut!

Highlighted
##

jonahk

Visitor

Re: Days of Supply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2019
10:46 AM

@Greg_Deckler Thanks for sharing! I've been using this Days of Supply calculation for an Inventory BI, but edited it to calculate on a monthly basis. Database is set up the same as yours. It’s been working great, but there is one more step. We forecast demand 6 months ahead, so the demand values for future months change each forecast. When the forecast data is updated, the Days of Supply of past months change because the calculation is using the most recent forcast data. I need to display a historical record of the days of supply calculated each month based on the forcast data we had at the time. How can I freeze the Days of Supply calculated for past months? I have added a column [Source] to identify in which month the data was forecast. I want to change this measure so it only uses the data from each individual forecast month to calculate Days for that forcast period. I've tried to do so by integrating calculate/filter functions into the measure, but keep running into errors. Here is my modified Measure:

Days = // Get the current Month and inventory for the current row VAR __week = MAX(Inventory[Month]) VAR __inventory = MAX(Inventory[Ending on hand Inventory]) // Create a table of all weeks greater than the current Month VAR __table = FILTER(ALL(Inventory),[Month]>__week) // Add our current inventory from above to each row VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory) // Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Month]<=EARLIER([Month])),[Demand])) // Add the difference in start versus the running total of demand to each row VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand]) // Create a table that only has the positive rows VAR __table4 = FILTER(__table3,[__left]>=0) // With only the positive rows, the MIN is the last row before demand runs out VAR __min = MINX(__table4,[__left]) // Therefore, our base days is the number of rows in this table * 30 VAR __baseDays = COUNTROWS(__table4)*30 // Grab the MAX value of the negative rows, this is the row right after our inventory runs out VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left]) // Divide the row right before the invetory ran out by the sum of the absolute values of right before and after // the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 30 // and this is the number of days in that Month before inventory ran out VAR __extraDays = __min / (__min + ABS(__max)) * 30 RETURN __baseDays + __extraDays

Let me know if you have any ideas. Thanks!

Sergii24

Frequent Visitor

Re: Days of Supply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2019
09:33 AM

Hi @Greg_Deckler ! Thank you very much for the great idea!

Currently I'm trying to expand it to the case, when there is a product hierachy: Brand->Prod Group->Product (SKU). In addition the same product have stock, production and forecst on different location (such as plants and distribution centers). The aim is to have a dynamic access to the Days of Supply based on the current selection.

The problem arises when I try to create _Table2

// Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand]))

The point is to correctly filter _table1 and use SUMX for the forecast but what happens is that depending on the situation I might filter or not filter some of the parameters (let's say, the location). Meaning that one time I can have 2 filters (week<Earlier[week] and SKU=Earlier[SKU]) in another case 3 filters (week<Earlier[week] and SKU=Earlier[SKU] and location=Earlier[Location]) and so on. What creates additional complexity is the situation when I select multiple locations (each has a string name), therefore I don't know how to "put them together" to a variable "Location".

Attached you can find a data sample: I want Days of Supply change dynamically depending on the level of hierarchy selected, including the situation, when only some wahrehouse is selected meaning only allocated part of inventory, forecast and production should be considered.

So the problem is to arrive to the following structure (depending on the hierarchy selected) described in your post as _table2.

Level 3 | Level 2 | Level 1 | |||||

Inventory | FrcstCummul | Inventory | FrcstCummul | Inventory | FrcstCummul | ||

12.694,5 | 886,0 | 24.788,1 | 4.294,0 | 24.425,4 | 4.447,0 | ||

12.694,5 | 1.734,0 | 24.788,1 | 7.948,0 | 24.425,4 | 8.247,0 | ||

12.694,5 | 2.278,0 | 24.788,1 | 10.629,0 | 24.425,4 | 11.067,0 | ||

12.694,5 | 2.818,0 | 24.788,1 | 13.014,0 | 24.425,4 | 13.601,0 | ||

12.694,5 | 3.304,0 | 24.788,1 | 16.165,0 | 24.425,4 | 16.915,0 | ||

12.694,5 | 5.416,0 | 24.788,1 | 21.259,0 | 24.425,4 | 22.205,0 | ||

12.694,5 | 7.929,0 | 24.788,1 | 28.964,0 | 24.425,4 | 30.159,0 | ||

12.694,5 | 9.684,0 | 24.788,1 | 33.069,0 | 24.425,4 | 34.404,0 | ||

12.694,5 | 11.268,0 | 24.788,1 | 38.012,0 | 24.425,4 | 39.490,0 | ||

12.694,5 | 12.992,0 | 24.788,1 | 42.336,0 | 24.425,4 | 43.939,0 | ||

12.694,5 | 14.704,0 | 24.788,1 | 45.967,0 | 24.425,4 | 47.680,0 |

Would be grateful if you can help to find the solution!

Thanks!