Suggestions for Measure Optimisation

[SpreadsheetNerd]

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 stumped but there is ONE measure that is really getting to me.

Through my internet sleuthing, I have found and modified a dynamic measure I found in another forum to calculate a rolling Week's cover. Essentially, it looks at the [Closing SOH] Measure, and then the [Demand Quantity] Measure to see, without any replenishment, how long the Closing SOH will last for; it then gives total days which I then divide by 7. It's not rocket science, it's supply and demand but I don't want averages and I want it to be filterable. 

Anyway, the calculation works, I've validated the output so one would think, happy days... HOWEVER, it takes too long. FAR too long and depending on filters, can run out of memory. 

I honestly do not know how to optimise it, or if it's even possible and welcome any help/suggestions that you have to optimise it. 

Weeks of Supply =

VAR CurrentSOH = [Closing SOH Quantity]

VAR MaxDate = MAX('Date'[Date])

VAR FutureWeeksTable = CALCULATETABLE(VALUES('Date'[Week Ending]),'Date'[Date] > MaxDate)

VAR OOSWeek =

FIRSTNONBLANK(

FutureWeeksTable,

VAR FutureWeeks = 'Date'[Week Ending]

VAR FutureDemand =

CALCULATE(

[Demand Quantity],

'Date'[Date] <= FutureWeeks,

'Date'[Date] > MaxDate

)

RETURN

IF(FutureDemand > CurrentSOH,1)

)

VAR WeeksSupply =

SWITCH(TRUE(),

OOSWEek = BLANK(), BLANK(),

CurrentSOH = BLANK(), BLANK(),

CurrentSOH < 0, 0,

NOT( ISBLANK(OOSWeek)),

VAR PartDays =

VAR FutureDemandExaustionWeek =

CALCULATE(

[Demand Quantity],

'Date'[Date] <= OOSWeek,

'Date'[Date] > MaxDate)

VAR ExcessDemand = FutureDemandExaustionWeek - CurrentSOH

VAR DemandInExhaustionWeek =

CALCULATE([Demand Quantity], OOSWeek, ALL('Date'))

RETURN

DIVIDE(FutureDemandExaustionWeek - ExcessDemand, FutureDemandExaustionWeek)

RETURN

OOSWeek - MaxDate - 1 + PartDays,

999

)

RETURN

DIVIDE(WeeksSupply,7,BLANK())

[Anonymous]

Hi @SpreadsheetNerd 

 You can try using DAX Studio to further analyze performance bottlenecks and check if there is high complexity in the calculations causing the performance drop.

Tutorials | DAX Studio

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@SpreadsheetNerd 

You 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

[shafiz_p]

Hi @SpreadsheetNerd  Try this:

WeeksOfSupply =
VAR CurrentSOH = [Closing SOH Quantity]
VAR MaxDate = MAX('Date'[Date])
VAR FutureWeeksTable = CALCULATETABLE(VALUES('Date'[Week Ending]), 'Date'[Date] > MaxDate)
VAR OOSWeek = 
    MINX(
        FILTER(
            FutureWeeksTable,
            CALCULATE(SUM('Date'[Date]), 'Date'[Date] <= EARLIER('Date'[Week Ending])) > CurrentSOH
        ),
        'Date'[Week Ending]
    )
VAR WeeksSupply =
    SWITCH(
        TRUE(),
        ISBLANK(OOSWeek), BLANK(),
        CurrentSOH = BLANK(), BLANK(),
        CurrentSOH < 0, 0,
        NOT(ISBLANK(OOSWeek)),
        VAR FutureDemandExhaustionWeek = 
            CALCULATE(
                [Demand Quantity],
                'Date'[Date] <= OOSWeek,
                'Date'[Date] > MaxDate
            )
        VAR ExcessDemand = FutureDemandExhaustionWeek - CurrentSOH
        VAR DemandInExhaustionWeek = 
            CALCULATE([Demand Quantity], OOSWeek, REMOVEFILTERS('Date'))
        VAR PartDays = DIVIDE(FutureDemandExhaustionWeek - ExcessDemand, DemandInExhaustionWeek)
        RETURN OOSWeek - MaxDate - 1 + PartDays,
        999
    )
RETURN DIVIDE(WeeksSupply, 7, BLANK())

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz