3-mo, 6-mo, and 12-mo 2 Year Stack Rolling Average

[Anonymous]

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. 

[technolog]

Step 1: Create the Running Totals for 3-Month, 6-Month, and 12-Month Periods
3-Month Running Total
M_3Mo_TY_Running_Total =
CALCULATE(
[TY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-3,
MONTH
)
)

M_3Mo_LLY_Running_Total =
CALCULATE(
[LLY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-3,
MONTH
)
)
6-Month Running Total
M_6Mo_TY_Running_Total =
CALCULATE(
[TY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-6,
MONTH
)
)

M_6Mo_LLY_Running_Total =
CALCULATE(
[LLY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-6,
MONTH
)
)
12-Month Running Total
M_12Mo_TY_Running_Total =
CALCULATE(
[TY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-12,
MONTH
)
)

M_12Mo_LLY_Running_Total =
CALCULATE(
[LLY_Comp_Sales],
DATESINPERIOD(
Cal_Day[Day_Date],
LASTDATE(Cal_Day[Day_Date]),
-12,
MONTH
)
)
Step 2: Calculate the Rolling Averages
3-Month Rolling Average
M_3Mo_Rolling_Avg =
DIVIDE(
[M_3Mo_TY_Running_Total],
3,
0
)
6-Month Rolling Average
M_6Mo_Rolling_Avg =
DIVIDE(
[M_6Mo_TY_Running_Total],
6,
0
)
12-Month Rolling Average
M_12Mo_Rolling_Avg =
DIVIDE(
[M_12Mo_TY_Running_Total],
12,
0
)
Step 3: Calculate the Year-Over-Year Change
3-Month Year-Over-Year Change
M_2YS_3Mo =
DIVIDE(
[M_3Mo_TY_Running_Total] - [M_3Mo_LLY_Running_Total],
[M_3Mo_LLY_Running_Total],
0
)
6-Month Year-Over-Year Change
M_2YS_6Mo =
DIVIDE(
[M_6Mo_TY_Running_Total] - [M_6Mo_LLY_Running_Total],
[M_6Mo_LLY_Running_Total],
0
)
12-Month Year-Over-Year Change
M_2YS_12Mo =
DIVIDE(
[M_12Mo_TY_Running_Total] - [M_12Mo_LLY_Running_Total],
[M_12Mo_LLY_Running_Total],
0
)