Optimizing a Forecasting measure

[fullcount]

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, incorporating the 5-week result in the 6-week forecast and so on.  Do you see any areas where I could optimize its performance?

Thanks!

Placements:= COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))

13 Wk Forecast:=

Var WKOnePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date]),-7,DAY))

Var WKTwoPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-7,-7,DAY))

Var WKThreePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-14,-7,DAY))

Var WKFourPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-21,-7,DAY))

Var WKFivePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-28,-7,DAY))

Var WKSixPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-35,-7,DAY))

Var WKFiftyOnePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-351,-7,DAY))

Var WKFiftyPlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-344,-7,DAY))

Var WKFortyNinePlacements = CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date])-337,-7,DAY))

And so on going back to 37 Weeks Ago...

Var TYPlacementTrend = ((WKOnePlacements+WKFivePlacements+WKFourPlacements+WKSixPlacements+WKTwoPlacements+WKThreePlacements)/6)

Var LYPlacementTrend = ((WKFiftyOnePlacements+WKFiftyPlacements+WKFortySixPlacements+WKFortySevenPlacements+WKFortyEightPlacements+WKFortyNinePlacements)/6)

Var WeightedPlacements = (TYPlacementTrend*[Weight for Distribution Trend Value])+(LYPlacementTrend*(1-[Weight for Distribution Trend Value]))

Var TYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],MAX(Sales[Date]),-42,DAY))

Var LYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-323),-42,DAY))

Var TYVelocity = TYUnits/TYPlacementTrend/6

Var LYVelocity = LYUnits/LYPlacementTrend/6

Var WeightedVelocity = (TYVelocity*[Weight for Velocity Trend Value])+(LYVelocity*(1-[Weight for Velocity Trend Value]))

Var CEProjection = WeightedVelocity*WeightedPlacements

Var FourWkProjection = IFERROR(CALCULATE(CEProjection*4),BLANK())

Var FiveWkLYPlacements = (WKFiftyPlacements+WKFortyEightPlacements+WKFortyFivePlacements+WKFortyNinePlacements+WKFortySevenPlacements+WKFortySixPlacements)/6

Var FiveWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-316),-42,DAY))

Var FiveWkLYVelocity = FiveWkLYUnits/FiveWkLYPlacements/6

Var FiveWkWeightedPlacements = (WeightedPlacements*[Weight for Distribution Trend Value])+(FiveWkLYPlacements*(1-[Weight for Distribution Trend Value]))

Var FiveWkWeightedVelocity = (WeightedVelocity*[Weight for Velocity Trend Value])+(FiveWkLYVelocity*(1-[Weight for Velocity Trend Value]))

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

Var FiveWkProjection = FourWkProjection+FifthWkProjection

Var SixWkLYUnits = Calculate([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-309),-42,DAY))

Var SixWkLYPlacements = (WKFortyNinePlacements+WKFortyEightPlacements+WKFortySevenPlacements+WKFortySixPlacements+WKFortyFivePlacements+WKFortyFourPlacements)/6

Var SixWkLYVelocity = SixWkLYUnits/SixWkLYPlacements/6

Var SixWkWeightedPlacements = (FiveWkWeightedPlacements*[Weight for Distribution Trend Value])+(SixWkLYPlacements*(1-[Weight for Distribution Trend Value]))

Var SixWkWeightedVelocity = (FiveWkWeightedVelocity*[Weight for Velocity Trend Value])+(SixWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var SixthWkProjection = IFERROR(SixWkWeightedPlacements*SixWkWeightedVelocity,BLANK())

Var SixWkProjection = FourWkProjection+FifthWkProjection+SixthWkProjection

Var SevenWkLYUnits = Calculate([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-302),-42,DAY))

Var SevenWkLYPLacements = (WKFortyEightPlacements+WKFortySevenPlacements+WKFortySixPlacements+WKFortyFivePlacements+WKFortyFourPlacements+WKFortyThreePlacements)/6

Var SevenWkLYVelocity = SevenWkLYUnits/SevenWkLYPLacements/6

Var SevenWkWeightedPlacements = (SixWkWeightedPlacements*[Weight for Distribution Trend Value])+(SevenWkLYPLacements*(1-[Weight for Distribution Trend Value]))

Var SevenWkWeightedVelocity = (SixWkWeightedVelocity*[Weight for Velocity Trend Value])+(SevenWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var SeventhWkProjection = IFERROR(SevenWkWeightedPlacements*SevenWkWeightedVelocity,BLANK())

Var SevenWkProjection = SixWkProjection+SeventhWkProjection

Var EightwkLYUnits = Calculate([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-295),-42,DAY))

Var EightWkLYPlacements = (WKFortySevenPlacements+WKFortySixPlacements+WKFortyFivePlacements+WKFortyFourPlacements+WKFortyThreePlacements+WKFortyTwoPlacements)/6

Var EightWkLYVelocity = EightwkLYUnits/EightWkLYPlacements/6

Var EightWkWeightedPlacements = (SevenWkWeightedPlacements*[Weight for Distribution Trend Value])+(EightWkLYPLacements*(1-[Weight for Distribution Trend Value]))

Var EightWkWeightedVelocity = (SevenWkWeightedVelocity*[Weight for Velocity Trend Value])+(EightWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var EighthWkProjection = IFERROR(EightWkWeightedPlacements*EightWkLYVelocity,BLANK())

Var EightWkProjection = SevenWkProjection+EighthWkProjection

Var NineWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-288),-42,DAY))

Var NineWkLYPlacements = (WKFortySixPlacements+WKFortyFivePlacements+WKFortyFourPlacements+WKFortyThreePlacements+WKFortyTwoPlacements+WKFortyOnePlacements)/6

Var NineWkLYVelocity = NineWkLYUnits/NineWkLYPlacements/6

Var NineWkWeightedPlacements = (EightWkWeightedPlacements*[Weight for Distribution Trend Value])+(NineWkLYPLacements*(1-[Weight for Distribution Trend Value]))

Var NineWkWeightedVelocity = (EightWkWeightedVelocity*[Weight for Velocity Trend Value])+(NineWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var NinthWkProjection = IFERROR(NineWkWeightedPlacements*NineWkWeightedVelocity,BLANK())

Var NineWkProjection = EightWkProjection+NinthWkProjection

Var TenWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-281),-42,DAY))

Var TenWkLYPlacements = (WKFortyFivePlacements+WKFortyFourPlacements+WKFortyThreePlacements+WKFortyTwoPlacements+WKFortyOnePlacements+WKFortyPlacements)/6

Var TenWkLYVelocity = TenWkLYUnits/TenWkLYPlacements/6

Var TenWkWeightedPlacements = (NineWkWeightedPlacements*[Weight for Distribution Trend Value])+(TenWkLYPLacements*(1-[Weight for Distribution Trend Value]))

Var TenWkWeightedVelocity = (NineWkWeightedVelocity*[Weight for Velocity Trend Value])+(TenWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var TenthWkProjection = IFERROR(TenWkWeightedPlacements*TenWkWeightedVelocity,BLANK())

Var TenWkProjection = NineWkProjection+TenthWkProjection

Var ElevenWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-274),-42,DAY))

Var ElevenWkLYPlacements = (WKFortyFourPlacements+WKFortyThreePlacements+WKFortyTwoPlacements+WKFortyOnePlacements+WKFortyPlacements+WKThirtyNinePlacements)/6

Var ElevenWkLYVelocity = ElevenWkLYUnits/ElevenWkLYPlacements/6

Var ElevenWkWeightedPlacements = (TenWkWeightedPlacements*[Weight for Distribution Trend Value])+(ElevenWkLYPlacements*(1-[Weight for Distribution Trend Value]))

Var ElevenWkWeightedVelocity = (TenWkWeightedVelocity*[Weight for Velocity Trend Value])+(ElevenWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var EleventhWkProjection = IFERROR(ElevenWkWeightedPlacements*ElevenWkWeightedVelocity,BLANK())

Var ElevenWkProjection = TenWkProjection+EleventhWkProjection

Var TwelveWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-267),-42,DAY))

Var TwelveWkLYPlacements = (WKFortyThreePlacements+WKFortyTwoPlacements+WKFortyOnePlacements+WKFortyPlacements+WKThirtyNinePlacements+WKThirtyEightPlacements)/6

Var TwelveWkLYVelocity = TwelveWkLYUnits/TwelveWkLYPlacements/6

Var TwelveWkWeightedPlacements = (ElevenWkWeightedPlacements*[Weight for Distribution Trend Value])+(TwelveWkLYPlacements*(1-[Weight for Distribution Trend Value]))

Var TwelveWkWeightedVelocity = (ElevenWkWeightedVelocity*[Weight for Velocity Trend Value])+(TwelveWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var TwelfthWkProjection = IFERROR(TwelveWkWeightedPlacements*TwelveWkWeightedVelocity,BLANK())

Var TwelveWkProjection = ElevenWkProjection+TwelfthWkProjection

Var ThirteenWkLYUnits = CALCULATE([Units],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Sales[Date])-260),-42,DAY))

Var ThirteenWkLYPlacements = (WKFortyTwoPlacements+WKFortyOnePlacements+WKFortyPlacements+WKThirtyNinePlacements+WKThirtyEightPlacements+WKThirtySevenPlacements)/6

Var ThirteenWkLYVelocity = ThirteenWkLYUnits/ThirteenWkLYPlacements/6

Var ThirteenWkWeightedPlacements = (TwelveWkWeightedPlacements*[Weight for Distribution Trend Value])+(ThirteenWkLYPlacements*(1-[Weight for Distribution Trend Value]))

Var ThirteenWkWeightedVelocity = (TwelveWkWeightedVelocity*[Weight for Velocity Trend Value])+(ThirteenWkLYVelocity*(1-[Weight for Velocity Trend Value]))

Var ThirteenthWkProjection = IFERROR(ThirteenWkWeightedPlacements*ThirteenWkWeightedVelocity,BLANK())

Var ThirteenWkProjection = TwelveWkProjection+ThirteenthWkProjection

RETURN

ThirteenWkProjection

It took ~4 minutes to load the DAX.  Here is how that query looks:

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Calendar_Lookup'[Date])),
'Calendar_Lookup'[Date] < (DATE(2021, 3, 31) + TIME(0, 0, 1))
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Calendar_Lookup'[Week Ending],
__DS0FilterTable,
"v_Forecast__13_Wk_Forecast_Units_Test", 'Depletions'[(Forecast) 13 Wk Forecast Units Test]
)

VAR __DS0IntersectionCount = CALCULATE(COUNTROWS(__DS0Core))

VAR __DS0BodyBinnedSample =
SAMPLEAXISWITHLOCALMINMAX(
3500,
__DS0Core,
'Calendar_Lookup'[Week Ending],
[v_Forecast__13_Wk_Forecast_Units_Test],
350,
,
ALPHABETICAL,
ASC,
105000,
60
)

EVALUATE
ROW(
"DS0IntersectionCount", __DS0IntersectionCount
)

EVALUATE
__DS0BodyBinnedSample

ORDER BY
'Calendar_Lookup'[Week Ending]

@jdbuchanan71 

[jdbuchanan71]

@fullcount 

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

[fullcount]

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.

[jdbuchanan71]

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.

[fullcount]

Let me know if this works: https://www.dropbox.com/scl/fi/vji0vi34ofcafnmuep5pw/Sample-Sales.xlsx?dl=0&rlkey=2n7o0j4p33enlwdmje...

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.

[jdbuchanan71 · Accepted solution]

@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.

[fullcount]

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