Forum Discussion

SpreadsheetNerd's avatar
SpreadsheetNerd
Regular Visitor
1 year ago

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

 

 

 

 

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.