Forum Discussion
Optimizing a Forecasting measure
- 4 years ago
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.
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.
- fullcount4 years agoFrequent Visitor
Thanks, jdbuchanan71! This has been very helpful. The first two steps trimmed about 25% of the time off, but it's still running pretty slowly at ~3 minutes.
I tried out the Calculated Column approach on Forecast Period and it worked exceptionally well (measure loaded in seconds). My issue is that it will only produce a value for the most recent sales date for any given item; I'd like to compare the forecasted performance with actual performance. To do that, I'll need MaxSalesDate to move backwards to whatever the selected value is for Calendar_Lookup[Date], which I think means I want Forecast Period to be a measure. But if I do that I'm thinking it will throw an error within the CALCULATE in all these variables. Is there an easy fix that you can see?
Thanks,
Ben