Measure or column that returns 5 year average for month - using different year ranges

Hi FLHound ,

 

Please try:

 

Here's the example data I created.

Date	Value
1/1/2016	10.9
2/1/2016	3.5
3/1/2016	1.9
4/1/2016	0.4
5/1/2016	5.8
9/1/2016	7.6
10/1/2016	5
11/1/2016	0
12/1/2016	0.6
1/1/2017	1.8
2/1/2017	0.8
3/1/2017	0.6
4/1/2017	1.8
5/1/2017	5.2
9/1/2017	16.8
10/1/2017	8.6
11/1/2017	1.1
12/1/2017	1.7
1/1/2018	1.1
2/1/2018	0.3
3/1/2018	0.5
4/1/2018	4.2
5/1/2018	12
9/1/2018	5.3
10/1/2018	1.5
11/1/2018	1.6
12/1/2018	1.1
1/1/2019	6.1
2/1/2019	1.3
3/1/2019	1.8
4/1/2019	1.6
5/1/2019	7
9/1/2019	1.6
10/1/2019	5.5
11/1/2019	1.5
12/1/2019	2.8
1/1/2020	1.2
2/1/2020	2.3
3/1/2020	0.4
4/1/2020	3.7
5/1/2020	6.3
9/1/2020	8.6
10/1/2020	8
11/1/2020	6.7
12/1/2020	3.1
1/1/2021	0.3
2/1/2021	1.3
3/1/2021	0.92
4/1/2021	3.2
5/1/2021	1
9/1/2021	8.2
10/1/2021	4
11/1/2021	3.1
12/1/2021	0.9
1/1/2022	1.4
2/1/2022	1
3/1/2022	1.28
4/1/2022	3
5/1/2022	6.2
9/1/2022	15.7
10/1/2022	3.6
11/1/2022	3.2
12/1/2022	1.2
1/1/2023	0.3
2/1/2023	0.4
3/1/2023	0.96
4/1/2023	9.1
5/1/2023	5.5
9/1/2023	11
10/1/2023	1.9
11/1/2023	3.3
12/1/2023	3.6
1/1/2024	2.6
2/1/2024	3.3
3/1/2024	3.36
4/1/2024	1
5/1/2024	0.8

 

First, create a Year column and a Month column:

Year = 'Table'[Date]. [Year]

Month = 'Table'[Date]. [Month]

 

Create a Calculated column to rank the years for each month:

YearRank = RANKX(FILTER(ALL('Table'), [Month] = EARLIER([Month])), 'Table'[Year],,ASC,Dense)

 

Create a MEASURE to identify the maximum year for each month.

MaxYearPerMonth = MAXX(FILTER(ALL('Table'), 'Table'[Month] = MAX('Table'[Month])), 'Table'[Year])

 

Create a calculated column to identify the corresponding Rank of the maximum year for each month.

MaxRank = CALCULATE(
    MAX('Table'[YearRank]),
    FILTER(
        ALL('Table'), FILTER(
        'Table'[Month] = EARLIER('Table'[Month])
    )
)

 

Finally, create a MEASURE to filter out the largest years and then average them.

Avg = 
AVERAGEX(
        FILTER(
          ALL('Table'),
            'Table'[YearRank] >= 'Table'[MaxRank] - 5 
            && 
            'Table'[YearRank] <= 'Table'[MaxRank] - 1 
            && 
            [Year] <> 'Table'[MaxYearPerMonth] 
            && 
            'Table'[Month] = MAX('Table'[Month])
        ),
        'Table'[Value]
    )

 

Put it into the visual object to display, the page effect is as follows:

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!