How to calculate Average growth rate over time Dax query

[Anonymous]

Let say
Number of milk cans sold by the company in the past four years:

2018 = 350
2019 = 450
2020 = 500
2021 = 650

Calculate the company's average annual growth rate.

So, if the present value is 650, the past value is 350 and the number of years is 4, you get:

Growth rate after 2018: (450 - 350) / 350 x 100 = 28.57%

Growth rate after 2019: (500 - 450) / 450 x 100 = 11.11%

Growth rate after 2020: (650 - 500) / 500 x 100 = 30%

Average growth rate over time = (28.57% + 11.11% + 30%) / 3 = 23.22% per year

Im struggling to get this in dax query .
Suggest a method to get Average growth rate over time

[amitchandak]

@Anonymous , Using a separate Year/Date table

measures

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Avg diff %

Rolling 3 = calculate(AverageX(Values('Date'[Year]),[diff%]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,Year))

or

CALCULATE(AverageX(Values('Date'[Year]),[diff%]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-3 && 'Date'[Year]<=max('Date'[Year])))

[Anonymous]

Hi,

Used the same example mentioned in question. It shows the present year value instead of average of total diff percent.
Measure i used is

Avg growth rate =
var This_Year = CALCULATE(sum('Table'[Sales]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
var Last_Year = CALCULATE(sum('Table'[Sales]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
var diff = This_Year-Last_Year
var diffpercent = divide(diff,Last_Year)*100
return
CALCULATE(AverageX(Values('Date'[Year]),diffpercent),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-3 && 'Date'[Year]<=max('Date'[Year])))

[Anonymous]

Hi,
Thanks a lot for the quick reply & query.
I'm going to check now.

If possible could you explain the Rolling 3 dax .
How it will dynamically calculate rate for each year and average the all three