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
Ozeroth said:
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 :)
Click to expand...

Hi Owen,

Many thanks for your response - that looks very promising indeed!

A couple of questions though, if you don't mind:


  • 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.)

  • 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?

If my waffle doesn't make sense, I'll mock something up to illustrate what I'd like to be able to do. Please, let me know.

I'm getting better at DAX, but it's not anyway near where I'd like it to be. So, if you could also add some comment to the code you posted, that would help my understanding a lot.

Many thanks again for you help. And also thanks to Matt for facilitating things!

Cheers,

Matty
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi Owen,

I have been having a good play with this today before the Xmas cheer robs me of my cognition! :)

Would you mind helping me to understand the following?


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


  • 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?


  • If I want to change '0' to '999' when Closing Stock > Demand , where in the formula do I make this change?

This solution is excellent and I would really like to better understand what is going on exactly, so if you could provide some comment lines in the code, that would really help.

Seasons Greetings!

Cheers,

Matty
 
Last edited:
Upvote 0
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 :)
 
Last edited:
Upvote 0
Hi Owen,

Thanks for the detailed reply - that all makes a lot of sense. :)

I'm just playing around with the solution again based on how my raw data is actually laid out. Given that it comes through as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Monday Date[/TH]
[TH]Product[/TH]
[TH]Demand[/TH]
[TH]Supply[/TH]
[TH]Opening Stock[/TH]
[/TR]
[TR]
[TD]18/12/2017[/TD]
[TD]Orange[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]Orange[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]Orange[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[TD]Orange[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD]Orange[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[TD]Orange[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]29/01/2018[/TD]
[TD]Orange[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]05/02/2018[/TD]
[TD]Orange[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]18/12/2017[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]Apple[/TD]
[TD]56[/TD]
[TD]0[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]Apple[/TD]
[TD]45[/TD]
[TD]0[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[TD]Apple[/TD]
[TD]6[/TD]
[TD]50[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD]Apple[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[TD]Apple[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]29/01/2018[/TD]
[TD]Apple[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD]05/02/2018[/TD]
[TD]Apple[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD]-27[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[TD]Banana[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]29/01/2018[/TD]
[TD]Banana[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]05/02/2018[/TD]
[TD]Banana[/TD]
[TD]7[/TD]
[TD]25[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

What will be the best (most efficient) way to get your solution to work?

I can see that creating the Demand table would be fairly straightforward, but things get a bit more messy trying to create the Supply table, as new records would need generating for 'Week 0' (i.e. 11/12/2017 based on the data above) in order to correctly calculate the first week's Opening Stock in the pivot table.

Could the Demand and Supply tables be generated automatically from the above using DAX? Or could the solution be tweaked another way?

Thanks in advance of your continued help.

Cheers,

Matty
 
Upvote 0
Hi Owen,

I've had a good play around with this and managed to work up a solution to automate the data transformation process, so everything's good!

I'm still keen to get a better understanding of the core measure, so if you could add some comments to the formula when you get chanced, that would be really help my understanding.

Thanks again for your help.

Cheers,

Matty
 
Upvote 0
Hi Matty, that's good to hear - I was out of commission for a few days so glad you've handled the data transformation.

Here is a commented version of the measures. I've also added it on a tab in the Dropbox workbook.

Code: 
=
// ====== Days of Supply measure ====== 
// NOTES:
// A. The calculation is performed as at the last week in the current filter context.
// B. For the sake of an example, I will use your figures for Apple in Week 3
// ============
// STEPS BELOW:
// ============
// 1. Get the current Closing Stock (as at the Max Week filtered).
//    For Apple in Week 3, CurrentStock = 19   
VAR CurrentStock = [Closing Stock]
// 2. Get the number of the Max Week filtered
//    For this example, MaxWeek = 3
VAR MaxWeek =
    MAX ( Week[Week] )
// 3. Create a single-column table of FutureWeeks
//    For Week 3, FutureWeeks = {4;5;6;7;8}
VAR FutureWeeks =
    CALCULATETABLE ( VALUES ( Week[Week] ), Week[Week] > MaxWeek, ALL ( Week ) )
// 4. Find the week where CurrentStock is exhausted
//    FIRSTNONBLANK is used to iterate over FutureWeeks and returns the first week where
//    FutureDemand >= CurrentStock.
//    where FutureDemand is the demand from MaxWeek + 1 to each of the future weeks
//    For Apple in Week 3, CurrentStock = 19,
//    and FutureDemand in Week 5 (=6+25) first exceeds 19, so StockExhaustionWeek = 5
VAR StockExhaustionWeek =
    FIRSTNONBLANK (
        FutureWeeks,
        VAR CandidateFutureWeek = Week[Week]
        VAR FutureDemand =
            CALCULATE (
                SUM ( Demand[Demand] ),
                Week[Week] <= CandidateFutureWeek,
                Week[Week] > MaxWeek,
                ALL ( Week )
            )
        RETURN
            // Note, this expression = 1 if FutureDemand >= CurrentStock and blank otherwise
            IF ( FutureDemand >= CurrentStock, 1 )
    )
// 5. DaysSupply is the final result, and is equal to 7 * ( StockExhaustionWeek - MaxWeek + <fraction of="" demand="" exhausting="" currentstock="" in="" stockexhaustionweek=""> - 1)
//    For Apple in Week 3, DaysSupply = 7 * (5 - 3 - 1 + 13/25) = 10.64
VAR DaysSupply =
    IF (
        // Return zero if CurrentStock is negative
        CurrentStock < 0,
        0,
        IF (
            // If there is a StockExhaustionWeek...
            NOT ( ISEMPTY ( StockExhaustionWeek ) ),
            // ...calculate fraction of last week's Demand that exhausts CurrentStock
            VAR Fraction =
                // FutureDemandToExhaustionWeek is the same Demand as calculated in step 4
                // for Week in MaxWeek + 1 to StockExhaustionWeek
                // In this example = 6 + 25 = 31
                VAR FutureDemandToExhaustionWeek =
                    CALCULATE (
                        SUM ( Demand[Demand] ),
                        Week[Week] <= StockExhaustionWeek,
                        Week[Week] > MaxWeek,
                        ALL ( Week )
                    )
                // ExcessDemand is demand in StockExhaustionWeek beyond that required to exhaust CurrentStock
                // In this example = 31 - 19 = 12
                VAR ExcessDemand = FutureDemandToExhaustionWeek - CurrentStock // DemandInExhaustionWeek is just the Demand in StockExhaustionWeek4
                // In this example = 25
                VAR DemandInExhaustionWeek =
                    CALCULATE ( [Demand Quantity], StockExhaustionWeek, ALL ( Week ) )
                RETURN
                    // Final Fraction in this example = (25 - 12)/25 = 13/25
                    DIVIDE (
                        DemandInExhaustionWeek - ExcessDemand,
                        DemandInExhaustionWeek
                    )
            RETURN
                // The Days Of Supply value = 7 * (# whole weeks + fraction of last week)
                // In this example = 7 * (5 - 3 - 1 + 13/25)
               7
                    * ( StockExhaustionWeek - MaxWeek - 1
                    + Fraction )
        )
    )
RETURN
    DaysSupply

Happy New Year!
Owen :)</fraction>
 
Upvote 0
Hi Owen,

Many thanks for the comprehensive commenting! :)

I will now study how the measure works and I'll come back if I have any further questions.

Thank you once again and I wish you a very Happy New Year!

Cheers,

Matty
 
Upvote 0
Hi Owen,

Apologies for coming back on this one again, but after carrying out further testing today, I've realised that the logic needs tweaking slightly.

Here's an example of what the measure is doing at the moment:

[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]55[/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]23[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Opening Stock[/TD]
[TD]0[/TD]
[TD]-10[/TD]
[TD]-25[/TD]
[TD]-37[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Closing Stock[/TD]
[TD]-10[/TD]
[TD]-25[/TD]
[TD]-37[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]-12[/TD]
[TD]-37[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Days of Supply[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]7.0[/TD]
[TD]0.0[/TD]
[TD]1.4[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
</tbody>[/TABLE]

Notice that because there is demand in weeks 1, 2 and 3, and no stock or supply in these weeks, when 50 boxes comes through in week 4 the stock is being consumed by demand that existed in earlier weeks. In reality, this demand has been lost and should not be carried forward.

What I actually need is this:

[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]55[/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]23[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Opening Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]37[/TD]
[TD]40[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Closing Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]37[/TD]
[TD]40[/TD]
[TD]25[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Days of Supply[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]21.3[/TD]
[TD]14.3[/TD]
[TD]10.2[/TD]
[TD]3.2[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Stock Out[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Here you can see that the demand in weeks 1, 2 and 3 is ignored, with this demand instead showing as a 'Stock Out' in those weeks.

Can things be tweaked to account for this logic change? And apologies again for overlooking this problem in my original request.

Cheers,

Matty
 
Upvote 0
Hi Matty,

No worries :)

Here is an updated Power Pivot model correcting for this:
https://www.dropbox.com/s/vucokk7yb4kysue/Days Stock correcting for negative stock.xlsx?dl=0

I handled this by:
  1. Creating a [Closing Stock Corrected] measure that is designed to be calculated per Product. It calculates [Closing Stock] as you have described, with Closing Stock floored at zero, with unmet Demand in a given week being lost and not carried forward.
    The logic applied in this measure is that the largest cumulative shortfall of Supply vs Demand encountered in any week up to the "current" week determines a "low water mark", i.e. the lowest point at which Closing Stock was reset to zero (stored in the variable MaxShortfallSoFar). Future weeks are then measured relative to this "low water mark", and [Closing Stock Corrected] is then Cumulative Supply less Demand with MaxShortFallSoFar added back (actually subtracted since it's a negative).
    Code: 
    [B]Closing Stock Corrected =
[/B]VAR SupplyLessDemand =
    [Supply Cumulative] - [Demand Cumulative]
VAR MaxWeek =
    MAX ( Week[Week] )
VAR MaxShortfallSoFar =
    MIN (
        CALCULATE (
            MINX (
                VALUES ( Week[Week] ),
                [Supply Cumulative] - [Demand Cumulative]
            ),
            Week[Week] <= MaxWeek,
            ALL ( Week )  
        ),
        0
    )
VAR ClosingStockCorrected =
    SupplyLessDemand - MaxShortfallSoFar
RETURN
    ClosingStockCorrected
  2. Changing [Closing Stock] so that is sums [Closing Stock Corrected] for each Product.
    Code: 
    [B]Closing Stock =[/B]
SUMX (
    VALUES ( 'Product'[Product] ),
    [Closing Stock Corrected]
)
  3. I added your negative Orange example but called the product Orange 2.
  4. I haven't created a [Stock Out] measure but you could easily calculate it from the other measures,
    e.g something like (-1)*MIN(OpeningStock + Supply - Demand, 0)

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
526
muhammadahmad21
M
S
  • Question Question
Available Week Data Capture
Replies
0
Views
159
shansakhi
S
P
  • Question Question
Calculating days of supply using DAX...2
Replies
0
Views
742
PetrS
P
S
  • Question Question
PowerBI Measure to calculate Sales Goals with Reports To: structure.
Replies
1
Views
274
Matt Allington
M
I
  • Question Question
Stock Cover calculation
Replies
2
Views
995
illusionek
I

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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