L3M Average for Same Period Last Year

Hi, I need to calculate last 3 month average for last year same period; I am using following DAX for L3M average;
CALCULATE(
[Volume],
DATESINPERIOD(
‘Calendar’[Date],
MAX(‘Calendar’[Date]),
-3,
MONTH
))
Now I am using following DAX formula to arrive at L3M Average for Same Period Last Year
VAR CurrentDate = MAX(‘Calendar’[Date])
VAR LastYearDate = DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate))
VAR LastYearEndDate = EOMONTH(LastYearDate, 0) – End of the month for the same period last year
VAR LastYearDates =
DATESINPERIOD(
‘Calendar’[Date],
LastYearEndDate,
-3,
MONTH
)
VAR MovingSumLastYear =
CALCULATE(
[L3M Avg Vol],
LastYearDates
)
VAR DistinctMonthYearCountLastYear =
CALCULATE(
DISTINCTCOUNT(‘Calendar’[MonthYear]),
LastYearDates
)
RETURN
IF(
ISBLANK(MovingSumLastYear),
BLANK(),
DIVIDE(MovingSumLastYear, DistinctMonthYearCountLastYear)
)
Now the thing is that I am getting Mar-24 data in Feb-24 data as well. Since 2024 is leap year hence I am getting incorrect numbers in Feb-24. Please help in in this regards. I have tried all the tricks and tried all formula but I am getting Feb-24 data incorrect everytime. Rest month data is absolutely correct.

Thanks & Regards
Harish Rathore

2 Likes

Hi @harishrathore - Please share the sample PBIX file.

Thanks
Ankit J

Hi @ankit , I am working on PowerPivot data model and would not be able to share data (apologies for that). if you any random dataset that would also work fine. I am only trying to get L3M average for last year same period. Only thing is that I am getting correct data for across months and years but for Feb-24 I am getting incorrect data. It might be due to leap year but I am not sure. I have spent hours and hours using different AI tools too but they were also not able to provide me accurate solutions. Then I had to come to this forum where I am sure that I will get help on this. Please help.

Thanks & Regards
Harish Rathore

Hi @harishrathore - Can try changing the logic like below to handle leap year issues.

VAR LastYearDate = if(month(CurrentDate) = 2 && day(CurrentDate) = 29,Date(year(CurrentDate) - 1,2,28),DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate)))

Thanks
Ankit J

3 Likes

Hi @ankit , Thank you so so much. You are a life savior. Its working perfectly fine now.
Thanks a lot again.

Thanks & Regards
Harish Rathore