Show 3 year growth measure over time

[ilcaa72]

I created a 3 year CAGR measure, 'Compouned Average Growth Rate'

3yr growth = calculate((divide([2016 subs],[2011 subs])^(1/3)-1))

but i have data from 1995 to 2016, so I want to see the 3 yr growth rate over time... so first value would be 1998. next would be 2001, etc

i know I hard coded the dates in my measure, how would i reference the dates in my new scenerio?  thanks

[v-jiascu-msft · Accepted solution]

Hi @ilcaa72,

Please check out the demo in the attachment.

CAGR =
VAR currentYearAmount =
    SUM ( Table1[Value] )
VAR threeYearLater =
    CALCULATE (
        SUM ( Table1[Value] ),
        PARALLELPERIOD ( 'Calendar'[Date], 3, YEAR )
    )
RETURN
    IF (
        ISBLANK ( threeYearLater ),
        BLANK (),
        POWER ( DIVIDE ( threeYearLater, currentYearAmount, 0 ), 1 / 3 )
            - 1
    )

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @ilcaa72,

Please check out the demo in the attachment.

CAGR =
VAR currentYearAmount =
    SUM ( Table1[Value] )
VAR threeYearLater =
    CALCULATE (
        SUM ( Table1[Value] ),
        PARALLELPERIOD ( 'Calendar'[Date], 3, YEAR )
    )
RETURN
    IF (
        ISBLANK ( threeYearLater ),
        BLANK (),
        POWER ( DIVIDE ( threeYearLater, currentYearAmount, 0 ), 1 / 3 )
            - 1
    )

Best Regards,

Dale

[ilcaa72 · Accepted solution]

thanks Dale,

i had to make sum small adjustments (YEAR should of been -3, not 3) and formula is (ending value/beg value)

besides that, it was perfect to calculate the CAGR running total.