cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Retail_Link New Member
New Member

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

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. 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 182 members 2,188 guests
Please welcome our newest community members: