3 year rolling average not working properly

[tgjones43]

Hi all

I have been using a measure to calculate a 3 year rolling average but what it is doing is calculating the average for each year and then averaging the 3 values, which is not correct. It needs to calculate the average of each 3 year period. This is the measure, which is using a date table:

3YearRollingMean =    CALCULATE(AVERAGEX(VALUES('Date'[Year]),CALCULATE(AVERAGE('Table'[Value]))),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,YEAR))

so when you have a dataset with a different number of values in each year this measure does not work.

For example, for this dataset the true 3 year average is 2.55. But the measure says 2.50 because it is averaging the average value for each year (i.e. 2020 average = 3.0, 2021 average = 2.5, 2022 average = 2.0). There is a difference because 2022 only has 3 values.

Date	Value
01/01/2020	3
01/04/2020	2
01/07/2020	4
01/10/2020	3
01/01/2021	1
01/04/2021	4
01/07/2021	2
01/10/2021	3
01/04/2022	3
01/07/2022	1
01/10/2022	2

Is there a measure that can calculate the true 3 year mean?

[rubayatyasmin · Accepted solution]

Hi, @tgjones43 

You need to slightly modify your dax

3YearRollingMean =
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Date] <= MAX('Table'[Date])
&& 'Table'[Date] > EDATE(MAX('Table'[Date]), -36)
)
)

t's important to note that this measure will calculate the rolling average for each row in your dataset, based on the past 3 years relative to the date of the row. Therefore, for rows in 2020, it will only calculate the average up to that point (since there are no previous years in your dataset). Starting from 2023, it will calculate the true 3 year average.

Please adapt this measure to fit the names of your tables and columns if necessary. It's also crucial to ensure that the date column is formatted as a Date data type in Power BI, not as Text or something else.

[rubayatyasmin · Accepted solution]

Hi, @tgjones43 

You need to slightly modify your dax

3YearRollingMean =
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Date] <= MAX('Table'[Date])
&& 'Table'[Date] > EDATE(MAX('Table'[Date]), -36)
)
)

t's important to note that this measure will calculate the rolling average for each row in your dataset, based on the past 3 years relative to the date of the row. Therefore, for rows in 2020, it will only calculate the average up to that point (since there are no previous years in your dataset). Starting from 2023, it will calculate the true 3 year average.

Please adapt this measure to fit the names of your tables and columns if necessary. It's also crucial to ensure that the date column is formatted as a Date data type in Power BI, not as Text or something else.

[tgjones43]

Hi @rubayatyasmin actually this isn't quite working how I expected...

The measure is looking for all values from the 36 month period prior to and including the last row in the 3rd year of the 3 year period. So in my dataset, in 2023 the last value I have is for 18/07/2023, so the measure is including all values back as far as August 2020, but it should only include values for the period 2021-2023.

Is that how you expect the measure to perform?

Thank you.

[rubayatyasmin · Accepted solution]

Hi, @tgjones43 

Based on your explanation, it seems like you want the rolling average to calculate only complete three-year periods. In other words, if your last data point is in the middle of 2023, you want the calculation to exclude all data from 2023 and only average the complete years 2020, 2021, and 2022.

You can achieve this by creating a new column to calculate the Year from the Date, and then modifying the measure to filter based on this Year column. Here's an example:

1. First, create a new column 'Year':

Table[Year] = YEAR(Table[Date])

Then, use this updated measure:

3YearRollingMean =
VAR CurrentYear = MAX('Table'[Year])
RETURN
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Year] <= CurrentYear
&& 'Table'[Year] > CurrentYear - 3
)
)

This measure will return the average of the 'Value' column for the current row's year and the two previous years. It should return blank for years earlier than 2022 because there aren't three years of data before those years. And as for the years later than 2023, the measure will exclude the incomplete year. If 'Table'[Year] includes a partial year, the measure will not count that year towards the 3-year period.

Did I help? If yes, hit 👍 and accept this answer as a solution. 

[tgjones43]

That's perfect @rubayatyasmin, thank you!

[rubayatyasmin]

happy to help. 

Appreciate the kudos. 🥂

[tgjones43]

Brilliant @rubayatyasmin , thanks so much! That's fine what you say about 2020. I'm going to built an IF statement into the formula to only calculate a mean if there are a certain number of datapoints within the 3 year period anyway.