cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hughfitz Frequent Visitor
Frequent Visitor

Three year averages & Change from previous period

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 OptionsStopover State/Region/SA2Country of residenceAge groupGenderBackpackersQuarterCount
Visitor nightsTotalFrance20 - 29MaleBackpackers1/03/2014192732
Visitor nightsTotalFrance20 - 29MaleBackpackers1/09/2012162940
Visitor nightsTotalGermany20 - 29FemaleBackpackers1/12/2015152807
Visitor nightsTotalGermany20 - 29FemaleBackpackers1/09/2018146843
Visitor nightsTotalNew Zealand30 - 54MaleNon backpackers1/03/2011145225
Visitor nightsTotalFrance20 - 29MaleBackpackers1/12/2013143274
Visitor nightsWestern AustraliaNew Zealand30 - 54MaleNon backpackers1/03/2011141901
Visitor nightsTotalUnited Kingdom20 - 29MaleBackpackers1/09/2010135442
Visitor nightsTotalGermany20 - 29MaleBackpackers1/09/2015127653
Visitor nightsTotalNew Zealand20 - 29MaleNon backpackers1/09/2008126823
Visitor nightsTotalFrance20 - 29MaleBackpackers1/09/2011119539
Visitor nightsTotalFrance20 - 29MaleNon backpackers1/06/2012119175
Visitor nightsTotalNew Zealand55+MaleNon backpackers1/12/2008117108
Visitor nightsTotalUnited Kingdom20 - 29MaleBackpackers1/12/2013109682
Visitor nightsQueenslandFrance20 - 29MaleNon backpackers1/06/2012108932
Visitor nightsTotalNew Zealand55+MaleNon backpackers1/09/2016106993
Visitor nightsTotalNew Zealand55+MaleNon backpackers1/09/2011106133
Visitor nightsTotalFrance20 - 29MaleBackpackers1/09/2013101771
Visitor nightsTotalFrance20 - 29MaleBackpackers1/03/200997724
Visitor nightsWestern AustraliaNew Zealand20 - 29MaleNon backpackers1/09/200897397
Visitor nightsTotalUnited Kingdom20 - 29MaleBackpackers1/03/201393338
Visitor nightsTotalNew Zealand30 - 54MaleNon backpackers1/03/201593222
Visitor nightsTotalFrance20 - 29MaleBackpackers1/12/201192766
Visitor nightsTotalGermany20 - 29FemaleBackpackers1/12/201792199
Visitor nightsTotalFrance20 - 29MaleBackpackers1/03/201190660
Visitor nightsQueenslandGermany20 - 29FemaleBackpackers1/09/201890367
Visitor nightsNorthern TerritoryUnited Kingdom20 - 29MaleBackpackers1/09/201090293
Visitor nightsTotalUnited Kingdom20 - 29FemaleBackpackers1/03/200590196
Visitor nightsTotalFrance20 - 29MaleBackpackers1/12/201589441
Visitor nightsTotalGermany20 - 29FemaleBackpackers1/09/201688685
Visitor nightsTotalFrance20 - 29MaleBackpackers1/06/201487328
Visitor nightsTotalUnited Kingdom20 - 29MaleBackpackers1/03/200586459
Visitor nightsTotalGermany30 - 54FemaleNon backpackers1/12/201386386
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Three year averages & Change from previous period

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

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Three year averages & Change from previous period

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