Forum Discussion
3 year rolling average not working properly
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.
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:
- 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.
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.
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:
- 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.
That's perfect rubayatyasmin, thank you!
happy to help.
Appreciate the kudos. 🥂