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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Interesting, but without knowing the units of each row metric, it would only be a guess. Is demand in units/week?
 
Upvote 0
cnestg8r said:
Interesting, but without knowing the units of each row metric, it would only be a guess. Is demand in units/week?
Click to expand...

Hi,

Demand, Supply, Opening Stock and Closing Stock are all the same unit of measure - let's say they're boxes.

Days of Supply is obviously in days, based on how long to Closing Stock lasts versus the future Demand.

Hope this clarifies.

Cheers,

Matty
 
Upvote 0
Matty said:
Hi Matt,

Thanks for replying.

I'm keen to see your suggestion, but the link isn't working. Could you resend it, please?

Cheers,

Matty
Click to expand...

Forget the above - the problem was my laptop.

I'll digest the solution and I'll come back with any questions.

Cheers!

Matty
 
Upvote 0
Hi Matt,

This looks a promising start, but it's days I need to calculate, not weeks.

To illustrate how this can be done using standard Excel functions:

Data as follows (occupying range A1:J11):

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Key Figure[/TH]
[TH]Week 1[/TH]
[TH]Week 2[/TH]
[TH]Week 3[/TH]
[TH]Week 4[/TH]
[TH]Week 5[/TH]
[TH]Week 6[/TH]
[TH]Week 7[/TH]
[TH]Week 8[/TH]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Demand[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Opening Stock[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]25[/TD]
[TD]13[/TD]
[TD]58[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Closing Stock[/TD]
[TD]40[/TD]
[TD]25[/TD]
[TD]13[/TD]
[TD]58[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Days of Supply[/TD]
[TD]28.0[/TD]
[TD]21.0[/TD]
[TD]14.0[/TD]
[TD]26.0[/TD]
[TD]19.0[/TD]
[TD]12.0[/TD]
[TD]5.0[/TD]
[TD]999.0[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Demand[/TD]
[TD]5[/TD]
[TD]56[/TD]
[TD]45[/TD]
[TD]6[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]15[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Opening Stock[/TD]
[TD]125[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]63[/TD]
[TD]38[/TD]
[TD]-12[/TD]
[TD]-27[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Closing Stock[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]63[/TD]
[TD]38[/TD]
[TD]-12[/TD]
[TD]-27[/TD]
[TD]-18[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Days of Supply[/TD]
[TD]24.6[/TD]
[TD]17.6[/TD]
[TD]10.6[/TD]
[TD]12.3[/TD]
[TD]5.3[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
</tbody>[/TABLE]

Formula copied across from C6 to J6 and C11 to I11 as follows:

Code: 
=IFERROR(IF(C5<=0,0,(SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D2:$J2,,,,COLUMN(D2:$J2)-COLUMN(D2)+1))<=C5))+LOOKUP(0,SUBTOTAL(9,OFFSET(D2:$J2,,,,COLUMN(D2:$J2)-COLUMN(D2)+1))-D2:$J2-C5,(C5-(SUBTOTAL(9,OFFSET(D2:$J2,,,,COLUMN(D2:$J2)-COLUMN(D2)+1))-D2:$J2))/D2:$J2))*7),999)

Taking C11 as an example, the formula breaks down as follows...

Calculate the number of 'complete weeks' covered by the Closing Stock:

Code: 
SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D7:$J7,,,,COLUMN(D7:$J7)-COLUMN(D7)+1))<=C10))

Which returns 3 (56+45+6 = 107).

Now, identify the first 'part week' covered by the Closing Stock, and calculate how much of that week is covered:

Code: 
LOOKUP(0,SUBTOTAL(9,OFFSET(D7:$J7,,,,COLUMN(D7:$J7)-COLUMN(D7)+1))-D7:$J7-C10,(C10-(SUBTOTAL(9,OFFSET(D7:$J7,,,,COLUMN(D7:$J7)-COLUMN(D7)+1))-D7:$J7))/D7:$J7)

Which returns 0.52 (120-107 = 13; 13/25 = 0.52).

The two are then added together (3.52) and multiplied by 7 to get total days (24.64).

A basic IF formula is used at the start to account for negative Closing Stock and the whole thing is wrapped in IFERROR to handle things when the Closing Stock exceeds the forward Demand.

Also, you'll see that there could be more than one product in the table, hence any solution would need to also consider this.

Hope this clarified what I'm trying to achieve and that it's possible to replicate the same logic using DAX.

Cheers,

Matty
 
Upvote 0
Hi Matty,

Matt alerted me to this post.
Here is a Power Pivot version that gives you the Days of Supply per Product (and aggregates if you are filtered on multiple Products).
https://www.dropbox.com/s/07gf1qxzfc8tfo3/Days Stock with Products.xlsx?dl=0

Note: I structured the data in 4 tables: Supply, Demand, Week, Product. Changing this would change the DAX of course.

The Days of Supply measures end up looking like this. You could combine them into a single measure if you wanted.
Code: 
[B]Days of Supply[/B]
=
SUMX ( 
    VALUES ( 'Product'[Product] ),
    [Days of Supply Core Measure]
)

Code: 
=
[B]Days of Supply Core Measure[/B]
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 )
        )
    )
RETURN
    DaysSupply

Also I used the convention that if Days of Supply is infinite then return blank. Of course you could change this to 999 as in your example.

Cheers
Owen :)
 
Last edited:
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,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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