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.
Hi all,
I work with data that comes in a quarterly, string format with several variables and filters that we apply.
I need to create a three-year moving average, and then measure change from the previous period e.g. (average of ’18, ’17, ’16 vs ’15, ’16, ’17).
An issue is that is also needs to be filtered by Quarter, i.e. just comparing March Quarter rolling averages and changes.
What I would like to get is a measure for 'Rolling 3 Year Annual' & 'Change from Previous Period'
Any help would be greatly appreciated. Happy to provide more information if required
Sample Data Table is here:
Summation Options | Stopover State/Region/SA2 | Country of residence | Age group | Gender | Backpackers | Quarter | Count |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/03/2014 | 192732 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/09/2012 | 162940 |
Visitor nights | Total | Germany | 20 - 29 | Female | Backpackers | 1/12/2015 | 152807 |
Visitor nights | Total | Germany | 20 - 29 | Female | Backpackers | 1/09/2018 | 146843 |
Visitor nights | Total | New Zealand | 30 - 54 | Male | Non backpackers | 1/03/2011 | 145225 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/12/2013 | 143274 |
Visitor nights | Western Australia | New Zealand | 30 - 54 | Male | Non backpackers | 1/03/2011 | 141901 |
Visitor nights | Total | United Kingdom | 20 - 29 | Male | Backpackers | 1/09/2010 | 135442 |
Visitor nights | Total | Germany | 20 - 29 | Male | Backpackers | 1/09/2015 | 127653 |
Visitor nights | Total | New Zealand | 20 - 29 | Male | Non backpackers | 1/09/2008 | 126823 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/09/2011 | 119539 |
Visitor nights | Total | France | 20 - 29 | Male | Non backpackers | 1/06/2012 | 119175 |
Visitor nights | Total | New Zealand | 55+ | Male | Non backpackers | 1/12/2008 | 117108 |
Visitor nights | Total | United Kingdom | 20 - 29 | Male | Backpackers | 1/12/2013 | 109682 |
Visitor nights | Queensland | France | 20 - 29 | Male | Non backpackers | 1/06/2012 | 108932 |
Visitor nights | Total | New Zealand | 55+ | Male | Non backpackers | 1/09/2016 | 106993 |
Visitor nights | Total | New Zealand | 55+ | Male | Non backpackers | 1/09/2011 | 106133 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/09/2013 | 101771 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/03/2009 | 97724 |
Visitor nights | Western Australia | New Zealand | 20 - 29 | Male | Non backpackers | 1/09/2008 | 97397 |
Visitor nights | Total | United Kingdom | 20 - 29 | Male | Backpackers | 1/03/2013 | 93338 |
Visitor nights | Total | New Zealand | 30 - 54 | Male | Non backpackers | 1/03/2015 | 93222 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/12/2011 | 92766 |
Visitor nights | Total | Germany | 20 - 29 | Female | Backpackers | 1/12/2017 | 92199 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/03/2011 | 90660 |
Visitor nights | Queensland | Germany | 20 - 29 | Female | Backpackers | 1/09/2018 | 90367 |
Visitor nights | Northern Territory | United Kingdom | 20 - 29 | Male | Backpackers | 1/09/2010 | 90293 |
Visitor nights | Total | United Kingdom | 20 - 29 | Female | Backpackers | 1/03/2005 | 90196 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/12/2015 | 89441 |
Visitor nights | Total | Germany | 20 - 29 | Female | Backpackers | 1/09/2016 | 88685 |
Visitor nights | Total | France | 20 - 29 | Male | Backpackers | 1/06/2014 | 87328 |
Visitor nights | Total | United Kingdom | 20 - 29 | Male | Backpackers | 1/03/2005 | 86459 |
Visitor nights | Total | Germany | 30 - 54 | Female | Non backpackers | 1/12/2013 | 86386 |
Solved! Go to Solution.
Hi @hughfitz
I don't see any other date field except the "Quarter" field, so when i calcuate the rolling average, i should use the "Quarter" field as date field, right?
You could follow these and have a try
DAX for Power BI: 12 Month Rolling Average
Best Regards
Maggie
Hi @hughfitz
I don't see any other date field except the "Quarter" field, so when i calcuate the rolling average, i should use the "Quarter" field as date field, right?
You could follow these and have a try
DAX for Power BI: 12 Month Rolling Average
Best Regards
Maggie
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |