Calculating Monthly Average Over 5 years with Context

[boley2131]

I'm about to pull my hair out and could use some help 😁. 

I'm working on a report where I want to show a comparison of monthly sales versus the 5yr average for that given month.  I've gotten the calculation to work, but the problem I'm facing is that I want the calculation to work when the date filter context is less than 5 years as well.  For example, I want the 5 year average of the month to work correctly when there is less than 5 years within the filter context.

Below is a look at a subset of my data where there is a row for each year/month:

Year	MonthShort	Industry Units
2023	Jun	303
2023	May	536
2023	Apr	1019
2023	Mar	974
2023	Feb	627
2023	Jan	651
2022	Dec	648
2022	Nov	247
2022	Oct	806
2022	Sep	340
2022	Aug	274
2022	Jul	447
2022	Jun	401
2022	May	700
2022	Apr	1163
2022	Mar	832
2022	Feb	491
2022	Jan	484
2021	Dec	569
2021	Nov	149
2021	Oct	346
2021	Sep	96
2021	Aug	84
2021	Jul	135
2021	Jun	268
2021	May	400
2021	Apr	913
2021	Mar	824
2021	Feb	471
2021	Jan	495

Below is the DAX code that I'm using which calculates the 5year average perfectly. 

Industry Units 5yr Avg 2 = 
VAR EndDate =
    MAX( 'Date'[Date] ) -- retrieves MAX Date   
VAR StartDate =
    EDATE( EndDate, -59 )  -- shifts EndDate to year beginning
VAR Result =
    AVERAGEX(
        CALCULATETABLE(
                SUMMARIZE('Date', 'Date'[Year], 'Date'[MonthName]),
                DATESBETWEEN('Date'[Date], StartDate, EndDate)
                ),
        'Measures Industry'[Industry Units])        
RETURN Result

Unfortunately, when a filter is applied on the date table the calculation pulls back the lastest month value.

Any help would be apprecited!

Thanks!

[sergej_og]

Just an idea.
Maybe with a small adjustment for VAR Result like this: so you will have a running AVG at the end.

VAR MaxDate = MAX('Calendar'[Date])
VAR Result = CALCULATE(
                AVERAGEX(
                    VALUES('Calendar'[MonthInCal]), [Sum Industry Units Measure]),
                    DATESINPERIOD('Calendar'[Date], MaxDate, -5, YEAR)
            ) 
--MonthInCall looks like this Jan 2019, Feb 2019... and so on

Maybe you have to create new calendar column.

[sevenhills]

Try changing this and see if it works

VAR EndDate = SELECTEDVALUE( 'Date'[Date] , MAX( 'Date'[Date] ) -- retrieves MAX Date   
                )