Three year averages & Change from previous period

[hughfitz]

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

[v-juanli-msft · Accepted 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