Forum Discussion
Suggestions for Measure Optimisation
Howdy! Long time lurker, first time poster. Normally, I have absolutely no issues googling all your helpful suggestions and figuring out my own DAX challenges; I can't say I've ever been truely...
Kedar_Pande
Super User
Super UserYou can try:
Weeks of Supply =
VAR CurrentSOH = [Closing SOH Quantity]
VAR MaxDate = MAX('Date'[Date])
VAR FutureDemandTable =
ADDCOLUMNS(
FILTER(
ALL('Date'),
'Date'[Date] > MaxDate
),
"CumulativeDemand",
CALCULATE(
SUM('Sales'[Demand Quantity]),
'Date'[Date] <= EARLIER('Date'[Date])
)
)
VAR ExhaustionDate =
MINX(
FILTER(
FutureDemandTable,
[CumulativeDemand] > CurrentSOH
),
'Date'[Date]
)
VAR DaysSupply =
IF(
ISBLANK(ExhaustionDate),
BLANK(),
DATEDIFF(MaxDate, ExhaustionDate, DAY)
)
RETURN
DIVIDE(DaysSupply, 7, BLANK())
💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn