Forum Discussion

fullcount's avatar
fullcount
Frequent Visitor
4 years ago
Solved

Optimizing a Forecasting measure

Hello,   I made a forecasting measure that produces great results, but takes way too long to load.  I suspect that one of the things I like about it is slowing it down—it iterates on itself, incorp...
  • jdbuchanan71's avatar
    4 years ago

    fullcount 

    Two things come to mind right off. 

    You are calculating MAX(Sales[Date]) many many times.  Instead you should put it into a variable then use the variable in the rest of your code:

    13 Wk Forecast:=
    VAR _MaxSalesDate = MAX(Sales[Date])
    
    Var WKOnePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate,-7,DAY))
    Var WKTwoPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate -7,-7,DAY))
    Var WKThreePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate -14,-7,DAY))
    Var WKFourPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate -21,-7,DAY))
    Var WKFivePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate -28,-7,DAY))
    Var WKSixPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date], _MaxSalesDate -35,-7,DAY))
    ...

     

    Second, you are using IFERROR a lot and that can be costly because it still has to do the calc, then get the error, then replace it.  Better to catch the error condition up front.  Figure out what condition causes the error then check for it.  For example, if the Var FifthWkProjection throws an error when the FiveWkWeightedPlacements = 0 you would replace this.

    Var FifthWkProjection = IFERROR(FiveWkWeightedPlacements*FiveWkWeightedVelocity,BLANK())

    with this

    Var FifthWkProjection = IF(FiveWkWeightedPlacements=0,BLANK(),FiveWkWeightedPlacements*FiveWkWeightedVelocity)

     

    Another possibility would be to add the week buckets to your Calendar_Lookup table.  Something like this should give you the 7 day buckets you are using in your code.  You would add it as a calculated column.

    Forecast Period Week = 
    VAR _MaxSales = MAX('Sales'[Date])
    VAR _PastDate = Dates[Date] <= _MaxSales
    RETURN 
    IF ( 
        _PastDate, 
        INT ( DIVIDE( _MaxSales - Dates[Date], 7) ) + 1
    )

    In my sample the MAX('Sales'[Date]) is 10/6/2021 so it looks like this:

    Then you could use the week bucket in your measure rather than the time intelligence functions.

    13 Wk Forecast:=
    
    Var WKOnePlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 1)
    Var WKTwoPlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 2)
    Var WKThreePlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 3)
    Var WKFourPlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 4)
    Var WKFivePlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 5)
    Var WKSixPlacements = CALCULATE([Placements],Calendar_Lookup[Forecast Period Week] = 6)
    ...
    

    The column would be updated when the model is refreshed so when the MAX('Sales'[Date]) changes it will adjust.

    I don't know it this would help speed it up, just throwing it out there.