Measure loads slowly

[738o51]

Hi Community,

I built a power bi that includes a measure for a rolling 5-day average. This rolling average can only include volume from non-weekends and non-holidays. I've gotten it to calculate correctly using the dax below, but it takes a while to run. Can anyone help me write this more efficiently?

Also, the business days index ranks business days in ascending order from today. In other words, the most recent business day is ranked 1 (today). The second most recent is ranked 2 (yesterday). Days in the date table that aren't business days are left blank.  

Rolling 5 day average_business days only = 

VAR TopRank = min(DateDim[Business days index])
Var RankUL = TopRank+5
VAR RollingSum = 
    sumx(
        filter(
            ALLSELECTED('Visit Details'),
                'Visit Details'[Business day index] < RankUL &&
                'Visit Details'[Business day index] >= TopRank),
        [Total Visits])    
Return Divide(RollingSum,5)

Thank you in advance for your help!

[Greg_Deckler · Accepted solution]

@738o51 Maybe try:

Rolling 5 day average_business days only = 
VAR TopRank = min(DateDim[Business days index])
Var RankUL = TopRank+5
VAR RollingSum = 
    CALCULATE
        SUM([Total Visits]),
        filter(
            ALLSELECTED('Visit Details'),
                'Visit Details'[Business day index] < RankUL &&
                'Visit Details'[Business day index] >= TopRank),
    )  
Return Divide(RollingSum,5)

[738o51]

@Greg_Deckler  Thank you!!

[Greg_Deckler]

@738o51 - Curious, how much did that speed things up?

[738o51]

My data set is pretty large so the change was significant. I went from about 2 minutes to load the graph to about 1 second. Thanks again!

@Greg_Deckler