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]

Just going to bump this one in case anyone has any ideas...

Cheers,

Matty

 

[cnestg8r]

Interesting, but without knowing the units of each row metric, it would only be a guess. Is demand in units/week?

 

[Matty]

Interesting, but without knowing the units of each row metric, it would only be a guess. Is demand in units/week?

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

 

[Matt Allington]

Well I have a solution, but it is not pretty! I would love to see some other solutions to the problem posted.
https://www.dropbox.com/s/fa1rkqlmgztcno0/forum Days Cover.xlsx?dl=1

 

[Matty]

Well I have a solution, but it is not pretty! I would love to see some other solutions to the problem posted.
https://www.dropbox.com/s/fa1rkqlmgztcno0/forum Days Cover.xlsx?dl=1

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

 

[Matty]

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

Forget the above - the problem was my laptop.

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

Cheers!

Matty

 

[Matty]

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):

Product	Key Figure	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8
Orange	Demand	10	15	12	5	8	20	15	21
Orange	Supply	0	0	0	50	0	0	0	30
Orange	Opening Stock	50	40	25	13	58	50	30	15
Orange	Closing Stock	40	25	13	58	50	30	15	24
Orange	Days of Supply	28.0	21.0	14.0	26.0	19.0	12.0	5.0	999.0
Apple	Demand	5	56	45	6	25	50	15	21
Apple	Supply	0	0	0	50	0	0	0	30
Apple	Opening Stock	125	120	64	19	63	38	-12	-27
Apple	Closing Stock	120	64	19	63	38	-12	-27	-18
Apple	Days of Supply	24.6	17.6	10.6	12.3	5.3	0.0	0.0	0.0

<tbody>
</tbody>

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

=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:

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:

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

 

[Ozeroth]

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.

[B]Days of Supply[/B]
=
SUMX ( 
    VALUES ( 'Product'[Product] ),
    [Days of Supply Core Measure]
)

=
[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

 

[Matt Allington]

Nested VAR - nice!