Rolling Average on a Percent Value

[Anonymous]

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:

Date

TransactionTypeID

TransactionDescription

DebitAmount

CreditAmount

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 

[Jihwan_Kim · Accepted solution]

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]

@Jihwan_Kim legend! That work just fine.

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

Cheers!

[Jihwan_Kim]

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]

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?