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