Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure loads slowly

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!

  • Anonymous 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)

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Anonymous 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)
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        Anonymous - Curious, how much did that speed things up?