- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

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
- Forums
- Get Help with Power BI
- Desktop
- Days of supply

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

shalini

Frequent Visitor

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

03-01-2019
06:10 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Greg_Deckler

Super User

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

03-03-2019
08:34 AM

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

Proud to be a Datanaut!

9 REPLIES 9

Greg_Deckler

Super User

Re: Days of supply

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

03-01-2019
07:17 AM

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

Proud to be a Datanaut!

shalini

Frequent Visitor

Re: Days of supply

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

03-01-2019
07:25 AM

hi hi

its taking demand over 28 weeks

shalini

Frequent Visitor

Re: Days of supply

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

03-01-2019
07:27 AM

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

Re: Days of supply

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

03-01-2019
07:28 AM

this vba code is done on a excel.

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

thanks

Shalini

Greg_Deckler

Super User

Re: Days of supply

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

03-01-2019
08:57 AM

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?

Proud to be a Datanaut!

shalini

Frequent Visitor

Re: Days of supply

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

03-01-2019
10:33 PM

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

Super User

Re: Days of supply

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

03-03-2019
07:43 AM

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:

- Start with inventory on hand of 49,320
- 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.
- Take the previous number, 1,901 and divide by the next week's demand, 6,924. This gives .27455...
- Multiply .27455 by 7, giving 1.922 days
- 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.

Proud to be a Datanaut!

Greg_Deckler

Super User

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

03-03-2019
08:34 AM

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

Proud to be a Datanaut!

shalini

Frequent Visitor

Re: Days of supply

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

03-04-2019
06:09 PM

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

thanks again !!