Calculating days of supply using DAX...

[Matty]

Hi Team,

I'm wondering if it's possible to create a DAX formula that will calculate days of supply, as shown in the example below:

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8
Demand	10	15	12	5	8	20	15	21
Supply	0	0	0	50	0	0	0	30
Opening Stock	50	40	25	13	58	50	30	15
Closing Stock	40	25	13	58	50	30	15	24
Days of Supply	28.0	21.0	14.0	26.0	19.0	12.0	5.0	0.0

<tbody>
</tbody>

It is possible to do it in Excel, but it relies on data being arranged in a crosstab in order for the formula to be able to calculate the forward buckets of demand covered.

Bit of a long shot I'm thinking, but I thought I'd ask the question in case anyone has any bright ideas.

Cheers,

Matty

 

[Matty]

That's great, Owen!

I had something similar in mind in my head, but I'm still not skilled enough with DAX yet to know what functions I can use to achieve the desired result.

It's a real shame there isn't a 'formula evaluator' for DAX like there is for standard Excel formulas, as this would be really useful for seeing the evaluation steps and debugging problems!

Thanks again for all of your help with this - it's really opened my eyes to what DAX can do!

Cheers,

Matty

 

[faati]

Hi,

The problem posted here is almost exactly what I m facing in powerquery (but for powerBI). The only difference is my data has another hierarchy (markets).
I tried using the formula presented here but it is not working for me. Can someone please help me on how to tweak it as per my data?

 

[faati]

My sample data for calculating the supply days: Data for cover days
The ending stock represent how much stock we are expecting to close the month with (we dont have supply or inbound data here)
Monthly sales are the total sales for a Product in a market in one month.

 

[Edilson]

Hi Matty, Hi Owen

I need to do the same calculations in powerbi. Could you provide the file in dropbox again or in another place?

The link here does not work anymore

https://www.dropbox.com/s/vucokk7yb4kysue/Days Stock correcting for negative stock.xlsx?dl=0

 

[Ozeroth]

Hi faati,

I had a look at your PBIX and have adjusted your measure to work with your model.

Link to updated PBIX here

Notes/Changes I made:

1. Added MonthEnd to your Date table (for convenience of calculation)
2. Marked your Date table as a date table (best practice with a Date table)
3. For your Supply Days measure to make sense, you must filter on a particular date where there is stock. If you leave Date unfiltered, you will likely get a 999 result as your Date table includes dates outside the data range.
4. I have interpreted the stock figures as month-end stock.
5. Measure ends up looking like this. I have adjusted it to use the Date table and it relies on the Sales/Stock data being monthly.
   

   Supply Days =VAR CurrentStock = [Stocks]
   VAR MaxDate =
   MAX ( 'Date'[Date])
   VAR FutureMonths =
   CALCULATETABLE ( VALUES ( 'Date'[MonthEnd] ), 'Date'[Date] > MaxDate )
   VAR StockExhaustionMonth =
   FIRSTNONBLANK (
   FutureMonths,
   VAR CandidateFutureMonth = 'Date'[MonthEnd]
   VAR FutureDemand =
   CALCULATE (
   SUM ( DaysCover[Monthly sales] ),
   'Date'[Date] <= CandidateFutureMonth,
   'Date'[Date] > MaxDate
   )
   RETURN
   IF ( FutureDemand >= CurrentStock, 1 )
   )
   VAR DaysSupply =
   IF ( CurrentStock < 0, 0,
   IF (
   NOT ( ISEMPTY ( StockExhaustionMonth ) ),
   VAR Fraction =
   VAR FutureDemandToExhaustionMonth =
   CALCULATE (
   SUM ( DaysCover[Monthly sales] ),
   'Date'[Date] <= StockExhaustionMonth,
   'Date'[Date] > MaxDate
   )
   VAR ExcessDemand = FutureDemandToExhaustionMonth - CurrentStock
   VAR DemandInExhaustionMonth =
   CALCULATE ( [Sales] , StockExhaustionMonth, ALL ( 'Date' ) )
   RETURN
   DIVIDE ( DemandInExhaustionMonth - ExcessDemand, DemandInExhaustionMonth )
   RETURN
   // Days to end of stock exhaustion month
   StockExhaustionMonth - MaxDate
   // less days in stock exhaustion month after exhausted
   - DAY ( StockExhaustionMonth ) * ( 1 - Fraction ),
   999
   )
   )
   RETURN
   DaysSupply

Kind regards,
Owen

My sample data for calculating the supply days: Data for cover days
The ending stock represent how much stock we are expecting to close the month with (we dont have supply or inbound data here)
Monthly sales are the total sales for a Product in a market in one month.

 

[comebakqueen]

I just came to say I love Owen; thank you! That solved my issue spectacularly!

 

[PetrS]

Season's Greetings to you too Matty!

First of all, I realised that the aggregation I was doing in my [Days of Supply] measure made no sense, and the [Days of Supply Core] measure actually does everything you need. So I have re-saved the Excel file with just one "days of supply" measure called [Days of Supply].

I will come back to you with a fully commented version of the measure, but for now here are answers to your questions:

1. How difficult would it be to display the individual Products as well as having an overall total? (By total, I mean summing up the Key Figures for each Product and then calculating the Days of Supply at the summed level.)
   The [Days of Supply] measure does just that, as you have noted below. It applies the same logic within any filter context
2. If I had a dimensions table linked to my Products, could I bring hierarchy elements from this in and see Products grouped up with the Days of Supply calculated at that level, too?
   Yes, the [Days of Supply] measure would work with any filter based on Product dimensions.
3. What is the purpose of the Days of Supply formula over the Days of Supply Core Measure formula? The reason I ask is because the latter returns correct results at both Product level and at an aggregated level, whereas the former does not.
   As I noted above, the original [Days of Supply] measure was not needed (and didn't make sense as it summed Days of Supply values), and the [Days of Supply Core] was correct for what you want to do, i.e. perform the same calculation at any level of aggregation.
4. The Opening Stock formula uses a variable called MinWeek, which evaluates to 0 in the example. But 1 is taken from MinWeek in the formula itself, which I'm struggling to understand. Could you clarify why it's needed, please?
   The [Opening Stock] measure calculates [Closing Stock] one week prior to the earliest week in the current filter context.
   For example, if you are filtered on Week 2, then [Opening Stock] is just [Closing Stock] in Week 1. Or if you are filtered on multiple weeks (such as Weeks 1-8 in the grand total of a PivotTable), then [Opening Stock] is the [Closing Stock] one week before the earliest filtered week.
   The variable MinWeek stores the earliest filtered Week, and subtracts one to change the filter context to the week one week prior.
   I should note that, for this exercise, I set up the source data such that the opening stock as at Week 1 is actually Supply in Week 0.
5. If I want to change '0' to '999' when Closing Stock > Demand , where in the formula do I make this change?
   The 999 would go measure would become the 'else' argument within
   IF ( NOT ( ISEMPTY ( StockExhaustionWeek ) ), ..., 999 )
   Here is the full measure with the 999 added:
   

   Days of Supply =
   VAR CurrentStock = [Closing Stock]
   VAR MaxWeek =
       MAX ( Week[Week] )
   VAR FutureWeeks =
       CALCULATETABLE ( VALUES ( Week[Week] ), Week[Week] > MaxWeek, ALL ( Week ) )
   VAR StockExhaustionWeek =
       FIRSTNONBLANK (
           FutureWeeks,
           VAR CandidateFutureWeek = Week[Week]
           VAR FutureDemand =
               CALCULATE (
                   SUM ( Demand[Demand] ),
                   Week[Week] <= CandidateFutureWeek,
                   Week[Week] > MaxWeek,
                   ALL ( Week )
               )
           RETURN
               IF ( FutureDemand >= CurrentStock, 1 )
       )
   VAR DaysSupply =
       IF ( CurrentStock < 0, 0,
           IF (
               NOT ( ISEMPTY ( StockExhaustionWeek ) ),
               VAR Fraction =
                   VAR FutureDemandToExhaustionWeek =
                       CALCULATE (
                           SUM ( Demand[Demand] ),
                           Week[Week] <= StockExhaustionWeek,
                           Week[Week] > MaxWeek,
                           ALL ( Week )
                       )
                   VAR ExcessDemand = FutureDemandToExhaustionWeek - CurrentStock
                   VAR DemandInExhaustionWeek =
                       CALCULATE ( [Demand Quantity], StockExhaustionWeek, ALL ( Week ) )
                   RETURN
                       DIVIDE ( DemandInExhaustionWeek - ExcessDemand, DemandInExhaustionWeek )
               RETURN
                   7
                       * ( StockExhaustionWeek - MaxWeek
                       + Fraction
                       - 1 ),
            [COLOR=#ff0000][B]999[/B][/COLOR]
           )
       )
   RETURN
       DaysSupply

Cheers,
Owen

Hello, Please what is the source for [Demand Quantity]? I am trying to replicate this code but I cant complete it. hank you.