Projections Using Avg Growth %

[cbtekrony]

I am computing future growth with this DAX:

PROJECTED GRWTH  AVG YOY CHANGE=

VAR vCurYear =

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

VAR vCurYearSales =

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

VAR vYearIncrement =

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

VAR vProjGrowth =

vCurYearSales * POWER ( 1 + [YoY Avg. Change], vYearIncrement )

VAR vResult =

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

RETURN

vResult

The forecast result I'm getting is the total of vCurYearSales  for each future year. If I plug in the "hard" number instead of my measure [YOY Avg Change] above, I get the correct forecast.  But of course, the point of DAX is to have dynamic results. 

Why can't I use my measure [YOY Avg Change] to compute this?

[cbtekrony · Accepted solution]

Solved! I sat on it for a few months and did a quick Google yesterday. I needed to use this particular calculation to figure out and keep dynamic the average percent change over time: 

VAR vGrowthRate =

AVERAGEX(

FILTER(ALLSELECTED('Calendar Table'),

'Calendar Table'[Date] <= MAX('Calendar Table'[Date])),

[YoY Change])

[v-kelly-msft]

Hi @cbtekrony ,

Could you pls provide a sample .pbix file for test?Remember to remove confidential information inside.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!