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 of the following columns:

 

DateTransactionTypeIDTransactionDescriptionDebitAmountCreditAmount

 

I'm calculating three measures:

  • Expenses =

CALCULATE (

        SUM (Transactions[Debit Amount]) - SUM (Transactions[Credit Amount]),
        Transactions[TransactionType ID] IN {"201", "202"}
)
 
  • Revenue = 
CALCULATE (
        SUM (Transactions[Credit Amount]) - SUM (Transactions[Debit Amount]),
        Transactions[TransactionType ID] IN {"101", "102"}
    )
 
  • Savings Rate = 
DIVIDE (
        [Revenue],
        [Expenses],
        0
    ) - 1
 
The goal, is to calculate a Rolling Average of the Savings Rate in the last 3 and 12 months. I have tried this suggestion (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-average-last-3-months/m-p/936915) but I'm not getting the result


Here it is the excel and PBI with some sample data:
Sample Data 

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]
    )

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
        Super 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.