cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
shalini Frequent Visitor
Frequent Visitor

Days of supply

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"

ProductWeekDemandEnding on hand InventoryDays of Supply
Product A2/4/2019049320?
Product A2/11/20191081938501?
Product A2/18/2019811439027?
Product A2/25/2019771731310 
Product A3/4/2019692324387 
Product A3/11/2019692377944 
Product A3/18/2019692371021 
Product A3/25/2019692481377 
Product A4/1/2019600075377 
Product A4/8/2019720068177 
Product A4/15/2019720060977 
Product A4/22/2019720053777 
Product A4/29/2019411849659 
Product A5/6/2019257747082 
Product A5/13/2019257744505 
Product A5/20/2019257741928 
Product A5/27/2019441137517 
Product A6/3/2019807729440 
Product A6/10/2019807730003 
Product A6/17/2019807721926 
Product A6/24/2019807713849 
Product A7/1/2019666615823 
Product A7/8/2019666717796 
Product A7/15/2019666711129 
Product A7/22/2019666613103 
Product A7/29/201944318672 
Product A8/5/201921946478 
Product A8/12/2019219412924 

 

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

ProductWeekDemandEnding on hand InventoryDays of Supply
Product A2/4/201904932044
Product A2/11/2019108193850137
Product A2/18/201981143902739
Product A2/25/201977173131032
Product A3/4/201969232438725
Product A3/11/201969237794495
Product A3/18/201969237102188
Product A3/25/201969248137796
Product A4/1/201960007537789
Product A4/8/201972006817782
Product A4/15/201972006097775
Product A4/22/201972005377768
Product A4/29/201941184965961
Product A5/6/201925774708254
Product A5/13/201925774450547
Product A5/20/201925774192840
Product A5/27/201944113751733
Product A6/3/201980772944026
Product A6/10/201980773000329
Product A6/17/201980772192622
Product A6/24/201980771384915
Product A7/1/201966661582317
Product A7/8/201966671779621
Product A7/15/201966671112914
Product A7/22/201966661310333
Product A7/29/20194431867226
Product A8/5/20192194647819
Product A8/12/201921941292422

 

thanks for your help in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Days of supply

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

9 REPLIES 9
Super User
Super User

Re: Days of supply

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

shalini Frequent Visitor
Frequent Visitor

Re: Days of supply

hi hi

 

its taking demand over 28 weeks

shalini Frequent Visitor
Frequent Visitor

Re: Days of supply

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 Frequent Visitor
Frequent Visitor

Re: Days of supply

this vba code is done on a excel.

 

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

 

thanks

Shalini

Super User
Super User

Re: Days of supply

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?


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

shalini Frequent Visitor
Frequent Visitor

Re: Days of supply

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.

DDS47419 (this is my total demand from 2/4/19 to 3/18/19in wk 3/18/2019 where my inventory(Ending on hand Inventory)  is falling under 0
DDS6923This is my DDS in that week (3/18/2019) whr inventory becomes 0
47419-6923 = 40496  
Ending on hand Inventory49320 
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.

Super User
Super User

Re: Days of supply

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Days of supply

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

shalini Frequent Visitor
Frequent Visitor

Re: Days of supply

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

 

thanks again !!

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 152 members 1,763 guests
Please welcome our newest community members: