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