Days of supply

[shalini]

hello

I am looking for a measure or calculated column in power BI to achieve "Days of Supply". 

1) I Have "Week"column (starting Monday), period = 28 weeks of data.

2) "Demand" column, which is also for running 28 weeks.

3) "Ending on Hand Inventory" column, where i have already considered the "inventory on hand" + "incoming inventory" for that week.

4) and last column is what i need "Days of Supply"

Product	Week	Demand	Ending on hand Inventory	Days of Supply
Product A	2/4/2019	0	49320	?
Product A	2/11/2019	10819	38501	?
Product A	2/18/2019	8114	39027	?
Product A	2/25/2019	7717	31310
Product A	3/4/2019	6923	24387
Product A	3/11/2019	6923	77944
Product A	3/18/2019	6923	71021
Product A	3/25/2019	6924	81377
Product A	4/1/2019	6000	75377
Product A	4/8/2019	7200	68177
Product A	4/15/2019	7200	60977
Product A	4/22/2019	7200	53777
Product A	4/29/2019	4118	49659
Product A	5/6/2019	2577	47082
Product A	5/13/2019	2577	44505
Product A	5/20/2019	2577	41928
Product A	5/27/2019	4411	37517
Product A	6/3/2019	8077	29440
Product A	6/10/2019	8077	30003
Product A	6/17/2019	8077	21926
Product A	6/24/2019	8077	13849
Product A	7/1/2019	6666	15823
Product A	7/8/2019	6667	17796
Product A	7/15/2019	6667	11129
Product A	7/22/2019	6666	13103
Product A	7/29/2019	4431	8672
Product A	8/5/2019	2194	6478
Product A	8/12/2019	2194	12924

The below table is with the example of result i am expecting.

Product	Week	Demand	Ending on hand Inventory	Days of Supply
Product A	2/4/2019	0	49320	44
Product A	2/11/2019	10819	38501	37
Product A	2/18/2019	8114	39027	39
Product A	2/25/2019	7717	31310	32
Product A	3/4/2019	6923	24387	25
Product A	3/11/2019	6923	77944	95
Product A	3/18/2019	6923	71021	88
Product A	3/25/2019	6924	81377	96
Product A	4/1/2019	6000	75377	89
Product A	4/8/2019	7200	68177	82
Product A	4/15/2019	7200	60977	75
Product A	4/22/2019	7200	53777	68
Product A	4/29/2019	4118	49659	61
Product A	5/6/2019	2577	47082	54
Product A	5/13/2019	2577	44505	47
Product A	5/20/2019	2577	41928	40
Product A	5/27/2019	4411	37517	33
Product A	6/3/2019	8077	29440	26
Product A	6/10/2019	8077	30003	29
Product A	6/17/2019	8077	21926	22
Product A	6/24/2019	8077	13849	15
Product A	7/1/2019	6666	15823	17
Product A	7/8/2019	6667	17796	21
Product A	7/15/2019	6667	11129	14
Product A	7/22/2019	6666	13103	33
Product A	7/29/2019	4431	8672	26
Product A	8/5/2019	2194	6478	19
Product A	8/12/2019	2194	12924	22

thanks for your help in advance

[Greg_Deckler]

@shalini - Can you explain how Days of Supply gets calculated? Is the Demand the anticipated demand for that week or over 28 weeks? I'm trying to understand where the 44 and the 37 come from in your top two lines.

[shalini]

hi hi

its taking demand over 28 weeks

[shalini]

the below is the vba code that's calculating: note sure if this helps:

'This function will expand the weeks of supply calculation to deal with weeks of supply in excess of
'nine weeks
Function NIWOS(EI, REQ1, REQ2, REQ3, REQ4, REQ5, REQ6, REQ7, REQ8, REQ9, REQ10, REQ11, REQ12, REQ13, _
REQ14, REQ15, REQ16, REQ17, REQ18, REQ19, REQ20, REQ21, REQ22, REQ23, REQ24)

Dim cTotalDemand As Currency 'This variable will contain the cummulative production demand (sum of REQs).

Dim iIndex As Integer 'This variable will contain the index # for the array.

Dim cDemand(23) As Currency ' this statement declares the array that will contain the REQ## values.

Dim cSupply As Currency ' this variable is a counter that will record the total of whole weeks of supply
'available.

Const cDAYSPERMONTH As Currency = 30.4 'This constant represents the average weeks per month.



'This process will load the requirements data to the array

cDemand(0) = REQ1
cDemand(1) = REQ2
cDemand(2) = REQ3
cDemand(3) = REQ4
cDemand(4) = REQ5
cDemand(5) = REQ6
cDemand(6) = REQ7
cDemand(7) = REQ8
cDemand(8) = REQ9
cDemand(9) = REQ10
cDemand(10) = REQ11
cDemand(11) = REQ12
cDemand(12) = REQ13
cDemand(13) = REQ14
cDemand(14) = REQ15
cDemand(15) = REQ16
cDemand(16) = REQ17
cDemand(17) = REQ18
cDemand(18) = REQ19
cDemand(19) = REQ20
cDemand(20) = REQ21
cDemand(21) = REQ22
cDemand(22) = REQ23
cDemand(23) = REQ24

'The next three statements initialize the local variables

cTotalDemand = 0

iIndex = 0

cSupply = 0

'This process will determine The build plan based on the desired months of supply.



Do Until EI - cTotalDemand <= 0 Or iIndex = 18

Let cTotalDemand = cTotalDemand + cDemand(iIndex)

If EI - cTotalDemand >= 0 Then
cSupply = cSupply + 30.4

ElseIf EI - cTotalDemand < 0 Then
cSupply = cSupply + (30.4 * ((EI - (cTotalDemand - cDemand(iIndex))) / cDemand(iIndex)))

End If

iIndex = iIndex + 1

Loop


NIWOS = cSupply



End Function

[shalini]

this vba code is done on a excel.

its basically looping every weeks demand (breaking into days) & Ending on hand.

thanks

Shalini

[Greg_Deckler]

OK, nothing posted thus far is really helping. If we go with 2/11/2019 and add up that row's demand and the next 4 rows, I get a total of 40,496, which is more than 38,501. This would indicate to me that starting on 2/11/2019 there are between 4 and 5 weeks of inventory versus demand. That would be between 28-35 days of inventory versus demand assuming 7 day weeks. Therefore, the 37 days indicated as the expected output seems out of range. What is the flaw in the logic above?

[shalini]

hi,

here is something i calcaulted as below:

- my starting inventory is 49320 & this will last until somewhere mid of wk 3/18/2019 & inventory on hand will become 0.

DDS	47419 (this is my total demand from 2/4/19 to 3/18/19	in wk 3/18/2019 where my inventory(Ending on hand Inventory)  is falling under 0
DDS	6923	This is my DDS in that week (3/18/2019) whr inventory becomes 0
47419-6923 = 40496
Ending on hand Inventory	49320
49320-40496 = 8824
8824/6923 = 1.274592
1.274592*7 =8.922144
8.922144+35 = 43.92214		35 days coming from (2/4/2019 - 3/18/2019)=35 days
so 43.92214 is the Days of supply

so This is the calculation thts looping in every week. 

so its always forward looking demand.

[Greg_Deckler · Accepted solution]

@shalini  - I believe I have the solution here:

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 less 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

Also, attached.

[Kunal8886]

Hello greg, can you create a solution for different product code plz. I have similar problem. But i have 5 different product codes, cab you redo the days supply formula and also the table formula for me plz

[Anonymous]

@Greg_Deckler Does this work for multiple products? or does the DAX needs to be modified?

[Greg_Deckler]

@Anonymous That would depend on the data but there is nothing that would intrinsically prohibit the approach from working with multiple products, maybe need an extra filter here or there. There is also a version of this in the Quick Measures Gallery as well as my book, DAX Cookbook. https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

[shalini]

thank you Greg!!! that was great !!!

thanks again !!

[Greg_Deckler]

OK, I'm starting to get the idea here, although I would have done it completely differently in terms of the calculation. For me, it seems like:

1. Start with inventory on hand of 49,320
2. Subtract weeks of demand until the next operation would make this a negative number (3/18/2019). At this week, inventory would be 1,901. This is 6 weeks or 42 days away.
3. Take the previous number, 1,901 and divide by the next week's demand, 6,924. This gives .27455...
4. Multiply .27455 by 7, giving 1.922 days
5. Add 42 and 1.922 days to give 43.922 days until inventory runs out.

Now that I understand the calculation, I'll try to come up with a DAX method of doing it. The tricky part is going to be the 2nd step because there's a looping aspect to it and DAX doesn't like looping.