cancel
Showing results for 
Search instead for 
Did you mean: 

Days of Supply

Super User
637 Views
Highlighted
Super User
Super User

Days of Supply

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.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!