Forecast using Average Growth Rate

[cbtekrony]

A user helped me forecast growth based on 2020 Growth (see Projected GrwthAS column). Now I am taking the AVERAGE GROWTH over time (averaging the Growth column from 2016 - 2020) to forecast (see Projected Grwth Avg YOY Change column). I thought I could take an average of the % Growth column (15.8%) and plug that calculation into DAX Growth Rate that I used for the Projected GrwthAS column. As you can see, the last column has the incorrect figures. 

Here is the DAX I used for the column that worked:

PROJECTED GRWTHAS =

VAR vCurYear =

CALCULATE ( YEAR ( MAX ( 'A Table'[Process Date] ) ), ALL('A Table') )

VAR vCurYearSales =

CALCULATE ( SUMX ( 'A Table', 'A Table'[AS ACV+YTD Est.DD] ), 'Calendar Table'[Year] = vCurYear )

VAR vLastYearSales =

CALCULATE (SUMX ( 'A Table', 'A Table'[AS ACV+YTD Est.DD]), 'Calendar Table'[Year] = vCurYear - 1 )

VAR vGrowthRate =

DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )

VAR vYearIncrement =

MAX ( 'Calendar Table'[Year] ) - vCurYear

VAR vProjGrowth =

vCurYearSales

* POWER ( 1 + vGrowthRate, vYearIncrement )

VAR vResult =

IF ( MAX ( 'Calendar Table'[Year] ) <= vCurYear, BLANK (), ROUND ( vProjGrowth, 0 ) )

RETURN

vResult

How do I rewrite this to use the average growth rate instead of the most recent growth rate? 

[lbendlin]

I would argue with your executives that the average growth rate is a distraction from what they should actually be focusing on which is on recovery (or repeating what they did in 2018).

Your growth rate is a derivative. Your growth rate change is  a double derivative. Ideally the growth rate change is a positive number.

Here's the data based on your example:

PY = CALCULATE(sum(GrowthRate[AS]),DATEADD(GrowthRate[Process],-1,YEAR))

GR = Divide(sum(GrowthRate[AS])-[PY],[PY])

PY GR = CALCULATE(GrowthRate[GR],DATEADD(GrowthRate[Process],-1,YEAR))

GR Deriv = DIVIDE(GrowthRate[GR]-GrowthRate[PY GR],GrowthRate[PY GR])

[cbtekrony]

Below is the data.

Process Date AS ACV+YTD Est. DD
12/1/2011 0:00 $160,993.33
12/1/2012 0:00 $1,404,452.45
12/1/2013 0:00 $2,472,531.63
12/1/2014 0:00 $4,010,372.16
12/1/2015 0:00 $5,295,672.74
12/1/2016 0:00 $6,553,524.60
12/1/2017 0:00 $7,571,281.68
12/1/2018 0:00 $8,999,105.40
12/1/2019 0:00 $10,069,232.56
12/1/2020 0:00 $10,995,139
The executives want to see the average growth rate.

So for example, from 2016 - 2020, the average growth rate is 15.8%. They want to see projections using this rate for the next few years. I can change this rate by using my slider for years. The calculation I used for this rate works in a card: 

AS YoY Avg. Change =
AVERAGEX(
    KEEPFILTERS(VALUES('Calendar Table'[Year])),
    CALCULATE([AS YoY Change])
)

But when I plug it into my forecast below it does not use this percentage to calculate.

Here is what I am trying to replace in my DAX above:

DIVIDE ( vCurYearSales - vLastYearSales, vLastYearSales )

[lbendlin]

First of all, why?  Your growth rate is (mostly) declining over the years. You don't want an average growth rate, you want a projection of the growth rate based on historic trends.  First do a modeling on your numbers 

Which would give a 7%-ish growth rate for 2021 and a 5.5% rate for 2022 etc.  Then use these values in your computation.  You need to use PRODUCTX() to get there.  Please provide sample data in usable format (not as a picture) if you like to see more.