Forum Discussion
3 Year Average per Month
Hi All,
I am trying to work out an average of X Value over the previous 3 years, not including the current year, by month and then filter by site. In my actual data I have 30+ sites.
My table looks like this:
| Site | Month | Month No | Year | Date | Average Windspeed |
| Bicker Fen | Jan | 1 | 2024 | 01/01/2024 | 7.912230769 |
| Bicker Fen | Feb | 2 | 2024 | 01/02/2024 | 6.938166667 |
| Bicker Fen | Mar | 3 | 2024 | 01/03/2024 | 6.656153846 |
| Bicker Fen | Jan | 1 | 2021 | 01/01/2021 | 6.51 |
| Bicker Fen | Feb | 2 | 2021 | 01/02/2021 | 7.2 |
| Bicker Fen | Mar | 3 | 2021 | 01/03/2021 | 6.4 |
| Bicker Fen | Jan | 1 | 2022 | 01/01/2022 | 6.4 |
| Bicker Fen | Feb | 2 | 2022 | 01/02/2022 | 9.5 |
| Bicker Fen | Mar | 3 | 2022 | 01/03/2022 | 5.7 |
I've tried a few different ways like average for windspeed where year is <2024 etc but to no luck.
Thanks in Advance,
M
Hi Anonymous
Try with this measure
AverageWindspeedLast3Years = CALCULATE( AVERAGEX( VALUES('Table'[Date]), 'Table'[Average Windspeed]), FILTER( 'Table', 'Table'[Year] < YEAR(TODAY()) && 'Table'[Year] >= YEAR(TODAY()) - 3), ALLEXCEPT('Table', 'Table'[Site], 'Table'[Month], 'Table'[Month No]) )Let me know if it works
3 Replies
- suparnababu8
Super User
Hi Anonymous
Try with this measure
AverageWindspeedLast3Years = CALCULATE( AVERAGEX( VALUES('Table'[Date]), 'Table'[Average Windspeed]), FILTER( 'Table', 'Table'[Year] < YEAR(TODAY()) && 'Table'[Year] >= YEAR(TODAY()) - 3), ALLEXCEPT('Table', 'Table'[Site], 'Table'[Month], 'Table'[Month No]) )Let me know if it works
Hi suparnababu8,
This worked perfectly! Thank you 🙂
- danextian
Hi Anonymous
Create a year table, relate it to your fact and create this measure:
Average = AVERAGEX ( ADDCOLUMNS ( SUMMARIZE ( --create a table of years wherein the years are from current year -3 to current year - 1 --note: if there are only two years in the current context, the sum of avg windspeed will be divided by two as well, one if one...etc FILTER ( ALL ( Years ), Years[Year] >= MAX ( Years[Year] ) - 3 && Years[Year] < MAX ( Years[Year] ) ), Years[Year] ), "Windspeed", CALCULATE ( SUM ( 'Table'[Average Windspeed] ) ) ), [Windspeed] )Mar 2024: 5.7+6.4 = 12.1/2 = 6.05
Please see attached sample pbix.
