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.
At this point I think we will need a dummy version of your model to play with. Can you create a version using excel as a data source that has all the same tables and columns, just with fake data? That is, unless you can share your actual .pbix file. Trying to performance tune measures without having access to the actual model is very difficult.
- fullcount4 years agoFrequent Visitor
Let me know if this works: https://www.dropbox.com/scl/fi/vji0vi34ofcafnmuep5pw/Sample-Sales.xlsx?dl=0&rlkey=2n7o0j4p33enlwdmjex0fw0p2
This is just a sample version of the Sales fact table; I'm using the Enterprise DNA date table function for Calendar_Lookup. I think that's the only data involved from the broader model here, which has lots more dimensions, but if it would help, I could make a version of the PBIX too.
Thank you again for all of your help with this stuff.