Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Forecast using Average Growth Rate

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? 
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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])
  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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 )