Calculating Average Growth Rate using last three years

[abbytank]

Hi everyone,

I am having issue with calculating the average growth rate based on preceding years value, as I calculate the yearly growth rate based on the previous ones value, I want to calculate average growth rate based on the values from last recent three years for example if 2019's rate is 4%, 2020's rate is 3% and 2021's rate is 5% I want average rate of 12/3 = 4%.

Here is sample snap of my data:

Sales ID	Product Name	Date	Net Sales
1	Mercedes	17/04/2019	£11,500.00
2	Belta	17/04/2020	£6,000.00
3	Lamborghini	28/04/2020	£3,650.00
4	Belta	29/06/2020	£12,500.00
5	Jaguar	29/07/2020	£9,950.00
6	Tacoma	14/08/2020	£5,500.00
7	Jaguar	14/08/2020	£35,000.00
8	Marill	14/08/2020	£45,500.00
9	Tacoma	24/09/2020	£14,590.00
10	Probox	12/10/2020	£16,500.00
11	Probox	18/10/2020	£19,500.00
12	Marill	23/10/2020	£65,500.00
13	Probox	27/10/2020	£9,500.00
14	Tacoma	03/11/2020	£3,500.00
15	Probox	10/11/2020	£68,900.00

and here is the example of what i want to achieve including the formulas I expect to use, if correct:

Product	Year	Sales Growth Rate (SGR)	Average Growth Rate
Mercedes	2019	0	(2019SGR + 2020SGR+2021SGR)/3
Mercedes	2020	(2020 sales - 2019)/2019 sales	(2019SGR + 2020SGR+2021SGR)/3
Mercedes	2021	(2021 - 2020)/2020	(2019SGR + 2020SGR+2021SGR)/3
Belta	2019	0	(2019SGR + 2020SGR+2021SGR)/3
Belta	2020	(2020 sales - 2019)/2019 sales	(2019SGR + 2020SGR+2021SGR)/3
Belta	2021	(2021 - 2020)/2020	(2019SGR + 2020SGR+2021SGR)/3
Tacoma	2019	0	(2019SGR + 2020SGR+2021SGR)/3
Tacoma	2020	(2020 sales - 2019)/2019 sales	(2019SGR + 2020SGR+2021SGR)/3
Tacoma	2021	(2021 - 2020)/2020	(2019SGR + 2020SGR+2021SGR)/3

Thank you in advance.

[FreemanZ · Accepted solution]

hi @abbytank 

you may

1) add a year column like this:

Year = YEAR([Date]),

2) plot a table measure with Product column, Year column and a measure like this:

SGR = 
VAR _year = SELECTEDVALUE(TableName[Year])
VAR _sales = SUM(TableName[Sales])
VAR _salespy =
CALCULATE(SUM(TableName[Sales]), TableName[Year] = _year-1)
VAR _variance =
DIVIDE(_sales - _salespy, _salespy)
RETURN
IF(
    ISBLANK(_salespy),
    0,
    _variance
)

For the average growth rate, not sure if that make sense to average a rate. It is more advisible to calculated the compund annual growth rate.