Calculating days of supply using DAX...

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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:


[TABLE="width: 610"]
<tbody>[TR]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Week 5[/TD]
[TD]Week 6[/TD]
[TD]Week 7[/TD]
[TD]Week 8[/TD]
[/TR]
[TR]
[TD]Demand[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Supply[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Opening Stock[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Closing Stock[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Days of Supply[/TD]
[TD="align: right"]28.0[/TD]
[TD="align: right"]21.0[/TD]
[TD="align: right"]14.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]19.0[/TD]
[TD="align: right"]12.0[/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]0.0[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?
 
Upvote 0
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.
 
Upvote 0
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.
    Code: 
    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

faati said:
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.
Click to expand...
 
Upvote 0
Ozeroth said:
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:
    Code: 
    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 :)
Click to expand...
Hello, Please what is the source for [Demand Quantity]? I am trying to replicate this code but I cant complete it. hank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Calculating Same Day Last Week only works partially in Power BI
Replies
2
Views
508
muhammadahmad21
M
S
  • Question Question
Available Week Data Capture
Replies
0
Views
154
shansakhi
S
P
  • Question Question
Calculating days of supply using DAX...2
Replies
0
Views
738
PetrS
P
S
  • Question Question
PowerBI Measure to calculate Sales Goals with Reports To: structure.
Replies
1
Views
266
Matt Allington
M
I
  • Question Question
Stock Cover calculation
Replies
2
Views
982
illusionek
I

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top