# Calculating days of supply using DAX...



## Matty (Nov 23, 2017)

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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Demand10151258201521Supply0005000030Opening Stock5040251358503015Closing Stock4025135850301524Days of Supply28.021.014.026.019.012.05.00.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 (Dec 21, 2017)

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

Cheers,

Matty


----------



## cnestg8r (Dec 21, 2017)

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


----------



## Matty (Dec 21, 2017)

cnestg8r said:


> 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 (Dec 21, 2017)

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 (Dec 22, 2017)

Matt Allington said:


> 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 (Dec 22, 2017)

Matty said:


> Hi Matt,
> 
> Thanks for replying.
> 
> ...



Forget the above - the problem was my laptop.

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

Cheers!

Matty


----------



## Matty (Dec 22, 2017)

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


ProductKey FigureWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8OrangeDemand10151258201521OrangeSupply0005000030OrangeOpening Stock5040251358503015OrangeClosing Stock4025135850301524OrangeDays of Supply28.021.014.026.019.012.05.0999.0AppleDemand55645625501521AppleSupply0005000030AppleOpening Stock12512064196338-12-27AppleClosing Stock12064196338-12-27-18AppleDays of Supply24.617.610.612.35.30.00.00.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 (Dec 22, 2017)

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 (Dec 22, 2017)

Nested VAR - nice!


----------



## Matty (Nov 23, 2017)

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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Demand10151258201521Supply0005000030Opening Stock5040251358503015Closing Stock4025135850301524Days of Supply28.021.014.026.019.012.05.00.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 (Dec 23, 2017)

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



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


----------



## Matty (Dec 24, 2017)

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


----------



## Ozeroth (Dec 25, 2017)

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:



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


----------



## nikkollai (Dec 26, 2017)

Owen, 
Great post.

Thanks


----------



## Matty (Dec 27, 2017)

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:


Monday DateProductDemandSupplyOpening Stock18/12/2017Orange1005025/12/2017Orange1504001/01/2018Orange1202508/01/2018Orange5501315/01/2018Orange805822/01/2018Orange2005029/01/2018Orange1503005/02/2018Orange21301518/12/2017Apple5012525/12/2017Apple56012001/01/2018Apple4506408/01/2018Apple6501915/01/2018Apple2506322/01/2018Apple5003829/01/2018Apple150-1205/02/2018Apple2130-2715/01/2018Banana025022/01/2018Banana502529/01/2018Banana1702005/02/2018Banana7253

<tbody>

</tbody>
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


----------



## Matty (Dec 30, 2017)

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


----------



## Ozeroth (Dec 31, 2017)

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.


```
=
// ====== 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>


----------



## Matty (Jan 1, 2018)

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


----------



## Matty (Jan 2, 2018)

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:


ProductKey FigureWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8OrangeDemand10151258201555OrangeSupply00050023030OrangeOpening Stock0-10-25-37803-12OrangeClosing Stock-10-25-37803-12-37OrangeDays of Supply0.00.00.07.00.01.40.00.0

<tbody>

</tbody>
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:


ProductKey FigureWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8OrangeDemand10151258201555OrangeSupply00050023030OrangeOpening Stock000045374025OrangeClosing Stock000453740250OrangeDays of Supply0.00.00.021.314.310.23.20.0OrangeStock Out10151200000

<tbody>

</tbody>
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


----------



## Ozeroth (Jan 4, 2018)

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:

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

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

Changing [Closing Stock] so that is sums [Closing Stock Corrected] for each Product.

```
[B]Closing Stock =[/B]
SUMX (
    VALUES ( 'Product'[Product] ),
    [Closing Stock Corrected]
)
```

I added your negative Orange example but called the product Orange 2.
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


----------



## Matty (Nov 23, 2017)

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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Demand10151258201521Supply0005000030Opening Stock5040251358503015Closing Stock4025135850301524Days of Supply28.021.014.026.019.012.05.00.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 (Jan 8, 2018)

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 (Apr 28, 2020)

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 (May 3, 2020)

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 (May 11, 2020)

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 (May 15, 2020)

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

Added MonthEnd to your Date table (for convenience of calculation)
Marked your Date table as a date table (best practice with a Date table)
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.
I have interpreted the stock figures as month-end stock.
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



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.


----------



## comebakqueen (Aug 12, 2021)

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


----------



## PetrS (Dec 13, 2021)

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


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


----------

