I’m hoping you can help me with this. I need to calculate what the (rolling) Average is for the last 4 (complete) quarters.
For example, I have this matrix table:
The values represent Total $$ Shipped in the quarter, broken down by week (13 weeks in a quarter). I need a calculation that can look back over the last 4 complete quarters (2017-Q2, 2017-Q3, 2017-Q4, 2018-Q1) and give me an average but WITHOUT using a filter.
I can get what I need if I have a filter in place. For instance, here are the correct averages if I used a filter:
But I need this to be automatic so when the next quarter is complete, it adjusts and the user doesn’t have to constantly change the filter.
NOTES: The “Week Number” refers to the week number in the quarter. Also, we use a 4-4-5 fiscal calendar and yes, I have a DATE table which is set up for fiscal dates.