Hello,
I am having trouble getting an accurate rolling average once the year changes. For instance, if I am looking at a 3-month rolling average from April, I get the correct numbers. However, if I look at the average from March, the numbers are off. I believe it has something to do with the year changing (from March I need to see the previous Feb, Jan, and Dec). The same thing happens with the 6-month and 12-month averages if I am looking at 2 different years.
Here are my formulas:
M_2YS_3Mo = [M_3Mo TY Running total]/[M_3Mo LLY Running Total]-1
M_3Mo TY Running Total = CALCULATE ([TY_Comp_Sales], DATESINPERIOD (Cal_Day[Day_Date], LASTDATE ( PREVIOUSMONTH ( Cal_Day[Day_Date] ) ), -3, MONTH))
M_3Mo LLY Running Total = CALCULATE ([LLY_Comp_Sales], DATESINPERIOD (Cal_Day[Day_Date], LASTDATE ( PREVIOUSMONTH ( Cal_Day[Day_Date] ) ), -3, MONTH))
Any thoughts would be greatly appreciated.
