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.
I didn't want a sample excel file, I wanted a sample .pbix using the excel data source. Please replicate your model using the excel file you loaded and add in the dates table and your measures play any other tables that are involved in the calculations.
I'll give you an example of what I am looking for. I was looking for some help with a measure so I created a topic that has a sample .pbix attached that replicates the data model involved. It makes it MUCH easier for user to help you and understand what you are trying to get to.
https://community.powerbi.com/t5/Desktop/Claim-completion-factor-and-IBNR/m-p/1780861#M696749
Thanks again, jdbuchanan71. In trying to build the sample PBIX out, everything has sped way up including the earlier fixes you suggested. I want to see if I can just do some recreating from here and clear up whatever is slowing things down in the main model.