Days of Supply 05-27-2019 10:46 AM - last edited 05-28-2019 12:59 PM

[Greg_Deckler]

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

[Kunal8886]

Hi @Greg_Deckler intried your solution as i have multiple code and it is glitched out can you tell me the correct dax to solve the problem. I need an urgent help

 

[Greg_Deckler]

@Kunal8886 It's a complex formula. Hard to say what is going wrong without sample source data and maybe your formula. Can you share a PBIX on OneDrive or Box or something?

[Kunal8886]

Hi @Greg_Deckler  can you go through the solution i am using and tell me if there any mistakes

[Kunal8886]

@Greg_Deckler The formula i am using is as follows

DaysOfSupplyMultipleCodes =

VAR __week = MAX('Inventory'[Week])

VAR __inventory = VALUES(Inventory[Inventory])

VAR __productCodes = {101,102}  -- Add your product codes here

VAR __table =

   FILTER(

       ALL(Inventory),

       [Week] > __week &&

       COUNTROWS(

           FILTER(__productCodes, 'Inventory'[Product Code] = EARLIER('Inventory'[Product Code]))

       ) > 0

   )

VAR __table1 = ADDCOLUMNS(__table, "__start", __inventory)

VAR __table2 = ADDCOLUMNS(__table1, "__demand", SUMX(FILTER(__table1, [Week] <= EARLIER([Week])), [Demand]))

VAR __table3 = ADDCOLUMNS(__table2, "__left", [__start] - [__demand])

VAR __table4 = FILTER(__table3, [__left] >= 0)

VAR __min = MINX(__table4, [__left])

VAR __baseDays = COUNTROWS(__table4) * 7

VAR __max = MAXX(FILTER(__table3, [__left] < 0), [__left])

VAR __extraDays =

   IF(ISBLANK(__max), 0, -- If there are no negative rows, set extra days to 0

       __min / (ABS(__min) + ABS(__max)) * 7

   )

RETURN

__baseDays + __extraDays And the result i want is as below and what i am able to get is also i have attached if you can plz reply

[Kunal8886]

@Greg_Deckler  I have a receiving quantity column but the value of inventory is receiving + inventory so if that will make a difference 

[Kunal8886]

The formula i am using is as below greg

DaysOfSupplyMultipleCodes =

VAR __week = MAX('Inventory'[Week])

VAR __inventory = VALUES(Inventory[Inventory])

VAR __productCodes = {101,102}  -- Add your product codes here

VAR __table =

   FILTER(

       ALL(Inventory),

       [Week] > __week &&

       COUNTROWS(

           FILTER(__productCodes, 'Inventory'[Product Code] = EARLIER('Inventory'[Product Code]))

       ) > 0

   )

VAR __table1 = ADDCOLUMNS(__table, "__start", __inventory)

VAR __table2 = ADDCOLUMNS(__table1, "__demand", SUMX(FILTER(__table1, [Week] <= EARLIER([Week])), [Demand]))

VAR __table3 = ADDCOLUMNS(__table2, "__left", [__start] - [__demand])

VAR __table4 = FILTER(__table3, [__left] >= 0)

VAR __min = MINX(__table4, [__left])

VAR __baseDays = COUNTROWS(__table4) * 7

VAR __max = MAXX(FILTER(__table3, [__left] < 0), [__left])

VAR __extraDays =

   IF(ISBLANK(__max), 0, -- If there are no negative rows, set extra days to 0

       __min / (ABS(__min) + ABS(__max)) * 7

   )

RETURN

__baseDays + __extraDays

[Anonymous]

Hi @Greg_Deckler 

I tried your solution and unfortunately the measure I created returned as blank.

My data table is structured:

Product Name

Product Brand

Product Category

Date

Forecast

Inventory

The date column is just weeks like in your example. I'm thinking maybe the difference is that mine has a product hierarchy?

The code I entered was therefore:

Days of Supply:=VAR __week = MAX('Stock Movements'[Date])
VAR __inventory = MAX('Stock Movements'[Inventory])
VAR __table = FILTER(ALL('Stock Movements'),'Stock Movements'[Date]>__week)
VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory)
VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,'Stock Movements'[Date]<=EARLIER('Stock Movements'[Date])),'Stock Movements'[Forecast]))
VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand])
VAR __table4 = FILTER(__table3,[__left]>=0)
VAR __min = MINX(__table4,[__left])
VAR __baseDays = COUNTROWS(__table4)*7
VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left])
VAR __extraDays = __min / (__min + ABS(__max)) * 7
RETURN
__baseDays + __extraDays

Unfortunately when entering this it returns blank for everything.

Do you have any idea why this would be the case and what I could do?

Many thanks,

Paddy

[Sergii24]

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!

[Anonymous]

@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!

[JHOLT]

Hi @Anonymous, I am having problems to do exactly what you detailed - fixing the forecast 'version' to specific months so that the historical months are not modified by new forecast data. Were you able to find a solution?