CODEWITHSUNDEEP
powerbidax
daOnlyBG
daOnlyBG

Reputation: 601

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

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:

  1. "closing date" for a given loan (column)
  2. loan count (measure)
  3. 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:

enter image description here

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?

Upvotes: 2

Views: 15499

Answers (1)

Krystian Sakowski
Krystian Sakowski

Reputation: 1653

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: Rolling Average Measure

Upvotes: 3

Related Questions