Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calcualte Last 3 Years Average

Hello,

 

I have the below dataset, I created the Financial Year column in the below dataset. Financial Year Starts on July 1st and ends on June 30th.

 

 

 

 

 

 

WeekValueFinancial Year
2017-06-W1302017
2017-06-W2402017
2017-07-W1502018
2017-07-W2402018
2018-07-W1302019
2018-O7-W2502019
2019-07-W1602020
2019-07-W2402020

2020-07-W1

302021

2020-07-W2

402021

 

 

What I am looking for is 

 

IF( [Financial Year]=2021, ([2020]+[2019]+[2018]/3),

    IF([Financial Year]<=2020, ([2019]+[2018]+[2017]/3)))

 

In the below chart, I need to show last 3 years average instead of 2017-2019 average.

 

 

If 2021 is present I shouldn't show 2020 in the column below chart, I should show the 2018-2020 average and 2021 column in the below chart.

 

 

Please let me know how to do it.

 

Thank you!

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , You can use a rolling measure. if required divide with the distinct count of year 

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,YEAR))/3
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,YEAR))  /3

     

2 Replies