How can I calculate a rolling average in DAX/PowerBI?

Question

After going through several posts on StackOverflow and the PowerBI forums, I still can't figure out how to calculate a rolling average based on a given period- in my case, a 30-day rolling average.

Most of the posts I've seen advocate something either identical or really similar to this:

Rolling Sum :=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( Sales ),
        [Date]
            >= MAX ( Sales[Date] ) - 365
            && [Date] <= MAX ( Sales[Date] )
    )
)

(code taken from this post)

...and yet, I can't seem to get the proper values.

In my case, I have the following:

"closing date" for a given loan (column)
loan count (measure)
closing length (column)- length of time (in days) to close a loan

What I'd like to calculate is the rolling 30 day average for any given day. I coded the following:

Rolling Average = 
    CALCULATE (
        SUM(Query1[Closing_Length])/[Loan Count],                                 
        FILTER (
            ALL ( Query1 ),                             
            [Closing Date].[Date]                                  
                >= MAX ( Query1[Closing Date] ) - 30
                && [Closing Date] <= MAX (  Query1[Closing Date] )
        )
    )

To check the results, I used a visual filter to examine one month's worth of data and these are the results:

Note the totals row at the bottom; for this given period, there are 102 loans and it took an aggregate of 3922 days for them to close. The average I'd like to calculate is 3922/102, which should equal approximately 38.45 days. Instead, we see 42.

How can I fix this?

@KrystianSakowski No, that post doesn't apply to a rolling 30 day period. It makes use of the SAMEPERIODLASTYEAR function, and for a rolling 30-day period, you can't use a "same period"-anything function for all days without getting errors. — daOnlyBG
Could you post expression or data for this member Query1[CTC Days])? — Krystian Sakowski
@KrystianSakowski Sorry, it should have been "Closing_Length"- fixed now. Thanks! — daOnlyBG

Krystian Sakowski Krystian Sakowski · Accepted Answer · 2017-04-18T05:45:05

Measure based solution:

Rolling Average Measure =
VAR A =
    SUMX (
        FILTER (
            ALL ( 'Query' ),
            'Query'[Closing Date] <= MAX ( 'Query'[Closing Date] )
        ),
        ROUND ( 'Query'[Closing Length], 2 )
    )
VAR B =
    SUMX (
        FILTER (
            ALL ( 'Query' ),
            'Query'[Closing Date] <= MAX ( 'Query'[Closing Date] )
        ),
        ROUND ( 'Query'[Loan Count], 2 )
    )
RETURN
    A / B

Calculated column based solution:

Rolling Average =
VAR CurrentDate = 'Query'[Closing Date]
VAR T =
    FILTER ( 'Query', [Closing Date] <= CurrentDate )
RETURN
    ROUND ( SUMX ( T, 'Query'[Closing Length] ) / SUMX ( T, [Loan Count] ), 2 )

Print Screen:

How can I calculate a rolling average in DAX/PowerBI?

1 Answers