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

[FLHound]

I'm sorry about the confusing title - hard to keep it to a one-liner! 
What I'm looking to do is get the the average of the top 5 years for each month, excluding the max year for that month. 
For example, the max year for September - December is 2023, so I want the average of the top 5 years EXCLUDING 2023 (2018-2022). The max year for January - May is 2024, so I want the average of 2019-2023.

I want to use these values in a combo column/line graph that already has a filter of filters applied to it through the filter pane, so it needs to ignore those. 

[Anonymous · Accepted solution]

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!