Average of the same month for the last 5 years

[MaryJones]

Hi would anyone know what dax I can use to calculate the average of the same month over the previous 5 years. So for Jan-23 the average over the previous 5 years for January would equal 20.8. 

thanks

For example:

Date	Cases
Jan-18	16
….	18
Dec-18	14
Jan-19	21
…	28
Dec-19	21
Jan-20	22
…	30
Dec-20	25
Jan-21	23
…	25
Dec-21	27
Jan-22	22
…	30
Dec-22	25
Jan-23	18

[Arul]

@MaryJones ,

Based on my assumption I have tried this formula, Could you use and let me know?

Last 5 Years Jan Average = 
VAR _startYear= YEAR(MAX(Table[Date]))-5
VAR _janSales = CALCULATE(
                        AVERAGE(Table[ Cases]),
                        MONTH(Table[Date])=1,
                        DATESINPERIOD(Table[Date],_startYear,5,YEAR))
RETURN _janSales

Thanks,

Arul

[FreemanZ]

hi @MaryJones 

With what columns are you going to present the needed measure?

[MaryJones]

Hi, the columns are Date and Cases.