Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
Regards
Tom
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
Hi Rose,
I can't access the file. It asked me to log in.
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |