How to create a rolling 3 month average with DAX

0
votes

I have a table like the below:

Name    Date1        Date2         Age
Tim     2020/05/20   2020/05/25    5
Tim     2020/07/10   2020/07/17    7
Tim     2020/08/04   2020/08/10    6
Tim     2020/09/05   2020/09/12    7
Tim     2020/08/05   2020/08/17    12

As you can see, Age is the difference between Date1 and Date2 and I would like to create a DAX expression to calculate the rolling 3 month average of Age (where Date1 is in the last 3 months).

Date1 is linked to my Calendar table.

The expected result for the above sample would be 8 (the first entry won't be included in the result as it's older than 3 months)

Any suggestions please?

2
powerbiaveragedax

2 Answers

1
votes

Considering last 3 month from TODAY() always, you can try this below measure to get your expected output. You can adjust the start and end date also if the logic is different. Below will always consider last 90 day including Today.

3_month_average = 

var start_3_month = TODAY() - 90
var end_3_month = TODAY()

RETURN
CALCULATE(
    AVERAGE(your_table_name[Age]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date1] >= start_3_month
            && your_table_name[Date1] <= end_3_month
    ) 
)
0
votes
MOVAVG_3M = 
VAR __LAST_DATE = LASTDATE('Table'[Date1].[Date])
RETURN
    AVERAGEX(
        DATESBETWEEN(
            'Table'[Date1].[Date],
            DATEADD(__LAST_DATE, -3, MONTH), __LAST_DATE),
        CALCULATE(SUM('Table'[Age]))
    )

You could as well goto Quick Measure - Rolling Average and pull in your fields and numbers.