Forum Discussion

abbytank's avatar
abbytank
Frequent Visitor
3 years ago
Solved

Calculating Average Growth Rate using last three years

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 IDProduct NameDateNet Sales
1Mercedes17/04/2019£11,500.00
2Belta17/04/2020£6,000.00
3Lamborghini28/04/2020£3,650.00
4Belta29/06/2020£12,500.00
5Jaguar29/07/2020£9,950.00
6Tacoma14/08/2020£5,500.00
7Jaguar14/08/2020£35,000.00
8Marill14/08/2020£45,500.00
9Tacoma24/09/2020£14,590.00
10Probox12/10/2020£16,500.00
11Probox18/10/2020£19,500.00
12Marill23/10/2020£65,500.00
13Probox27/10/2020£9,500.00
14Tacoma03/11/2020£3,500.00
15Probox10/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:

ProductYearSales Growth Rate (SGR)Average Growth Rate
Mercedes20190(2019SGR + 2020SGR+2021SGR)/3
Mercedes2020(2020 sales - 2019)/2019 sales(2019SGR + 2020SGR+2021SGR)/3
Mercedes2021(2021 - 2020)/2020(2019SGR + 2020SGR+2021SGR)/3
Belta20190(2019SGR + 2020SGR+2021SGR)/3
Belta2020(2020 sales - 2019)/2019 sales(2019SGR + 2020SGR+2021SGR)/3
Belta2021(2021 - 2020)/2020(2019SGR + 2020SGR+2021SGR)/3
Tacoma20190(2019SGR + 2020SGR+2021SGR)/3
Tacoma2020(2020 sales - 2019)/2019 sales(2019SGR + 2020SGR+2021SGR)/3
Tacoma2021(2021 - 2020)/2020(2019SGR + 2020SGR+2021SGR)/3

 

Thank you in advance.

@Daxhelp
@NeedHelp
growth rate
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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. 

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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.