Averaging Rolling Quarters without Filter

[Roseventura]

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 $$ 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.

[TomMartens]

Hey,

it would be really helpful if you could create a pbix file that contains sample data and upload the file to onedrive or dropbox and finally share the link to this file.

You mention the following:

• complete quarter
  How can a quarter be recognized as complete, I assume there are week nombers assigned  to quarters, and if data is available for the latest week or day of the quarter, the quarter will be recognized as complete?
• 4-4-5 fiscal calendar
  At least for me this is a somewhat unusual calendar, for this reason it will help me a lot if do not have to recreate a date table by myself

Regards

Tom

[Roseventura]

Tom,

Here is a copy of my sample file:  https://arrowelectronics-my.sharepoint.com/:u:/r/personal/rventura_arrow_com/Documents/Sample.pbix?c...

You can't necessarily tell if the quarter is complete simply by checking to see if week 13 has values > 0.  As soon as a quarter starts, there would be values > 0  (one would hope!)  So I've identified what the current quarter is (see those measures), and then start with the quarter prior to that.

Let me know if you need more info or if you can't open the above link.

Rose

[v-jiascu-msft]

Hi Rose,

I can't access the file. It asked me to log in.

Best Regards,

Dale