Forum Discussion

Roseventura's avatar
Roseventura
Responsive Resident
8 years ago

Averaging Rolling Quarters without Filter

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.

 

 

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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