Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

rolling average without time intelligence

I have a measure ([Total Sales]) that I want to compute a 6-week rolling average on.  The "date" grain of my data model is week.  So, I am unable to use the quick measure feature to auto-generate a r...
  • Anonymous's avatar
    Anonymous
    5 years ago

    I solved my problem with the following:

    Rolling 6 Weeks Average of Total Sales = 
    
    //get the given week
    VAR __given_week =
        SELECTEDVALUE('Dimension Week'[Week Number])
    
    //number of weeks in rolling average
    VAR __duration =
        6
    
    //create a table of weekly ranges per week
    VAR __calculation_range =
        FILTER(
            ALL('Dimension Week')
            ,[Week Number] <= __given_week
            && [Week Number] > __given_week - __duration
        )
    
    RETURN
        IF(
            COUNTROWS(__calculation_range) = __duration //only return the result when there are the desired number of weeks in the range (e.g., week 5 doesn't have 6 weeks in it)
           ,CALCULATE(
                AVERAGEX(
                        'Dimension Week'
                        ,[Total Sales]
                )
                ,__calculation_range
            )
            ,BLANK()
        )