Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rolling Average on a Percent Value

Hi everyone, I am having trouble trying to calculate a rolling average of a percent measure.   I have a Calendar Table that is connected to a Transactions Table. The Transactions Table is made o...
Rolling Averages
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I recreated a calendar table, and connected to Transactions table.

     

     

     

    Savings rate 12 months rolling avg: = 
VAR _currentmonth =
    EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
VAR _elevenmonthsago =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -12 ) + 1
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= _elevenmonthsago
                    && 'Calendar'[Date] <= _currentmonth
            ),
            'Calendar'[Month-Year],
            'Calendar'[Month-Year sort]
        ),
        [Savings Rate]
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I recreated a calendar table, and connected to Transactions table.

 

 

 

Savings rate 12 months rolling avg: = 
VAR _currentmonth =
    EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
VAR _elevenmonthsago =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -12 ) + 1
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= _elevenmonthsago
                    && 'Calendar'[Date] <= _currentmonth
            ),
            'Calendar'[Month-Year],
            'Calendar'[Month-Year sort]
        ),
        [Savings Rate]
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Jihwan_Kim legend! That work just fine.

    Btw, why did you add the [Month-Year sort] to the SUMMARIZE function?

    Cheers!

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      Thank you for your feedback.

      This is just one of my habits when I consider including columns in filter coditions in a measure. If one column is sorted by another column, not always but most of the time, it is better to have two of them in order to show a correct number.

      You can try my measure without including sort column and check how the numbers are.

      I hope this helps.

      Thank you. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Yeah, it didn't work that well without the sort column 😅.

        Wouldn't the same result be achievable by summarizing with the [Year] and [Month] columns?