Myujth

I want to calculate the annual average of sales by quarter in Power Bi.

I usually solve this in Excel with an Averageif between dates (last date of a quarter and same date one year earlier).

Below a sample of my data:

 Date Quarter Sale
1/12/2016 2016-Q1 12.5
2/25/2016 2016-Q1 65.1
4/7/2016 2016-Q2 95.5
6/22/2016 2016-Q2 74.5
7/10/2016 2016-Q3 7.3
8/30/2016 2016-Q3 87.6
9/5/2016 2016-Q3 88.4
10/27/2016 2016-Q4 18
11/12/2016 2016-Q4 64.2
12/29/2016 2016-Q4 37.2
1/28/2017 2017-Q1 17.8
3/8/2017 2017-Q1 59.6
4/16/2017 2017-Q2 68.7
6/15/2017 2017-Q2 68.5
7/20/2017 2017-Q3 61.8
8/7/2017 2017-Q3 10.8
9/23/2017 2017-Q3 26.5
10/7/2017 2017-Q4 49.8
11/26/2017 2017-Q4 79.7
12/3/2017 2017-Q4 80.5
1/18/2018 2018-Q1 12.5
3/19/2018 2018-Q1 54.7
4/12/2018 2018-Q2 64.0
6/19/2018 2018-Q2 58.9
7/29/2018 2018-Q3 59.9
8/9/2018 2018-Q3 4.1
9/13/2018 2018-Q3 20.2

The wanted result is the table below:

Quarter1_Yr_Trailing Avg Sale 
2017-Q1 52.3
2017-Q2 56.7
2017-Q3 43.3
2017-Q4 52.4
2018-Q1 51.3
2018-Q2 49.9
2018-Q3 48.4

Just to clarify, 2018-Q3 is the average of any sale that was recorded between Sept 30, 2017 and Sept 30, 2018:

10/7/2017 2017-Q4 49.8
11/26/2017 2017-Q4 79.7
12/3/2017 2017-Q4 80.5
1/18/2018 2018-Q1 12.5
3/19/2018 2018-Q1 54.7
4/12/2018 2018-Q2 64.0
6/19/2018 2018-Q2 58.9
7/29/2018 2018-Q3 59.9
8/9/2018 2018-Q3 4.1
9/13/2018 2018-Q3 20.2

Thanks for your help.

regards,
Simon