Calculate CAGR based on dynamic range

[ingyelmessiry]

I am trying to calculate CAGR based on dynamic range, where the user filter on the start date and end date.
example
Start date: 2017
Dax function :
CAGR Start Year Value =
CALCULATE (SUM ('Raw Data'[Value] ),FILTERS('Start Year Table'[Year]))

End Year: 2023
Dax function:
CAGR End Year Value =CALCULATE (SUM ('Raw Data'[Value] ),FILTER ( ALLSELECTED ( 'Raw Data' ), 'Raw Data'[Year - End] = T_Measures[CAGR selected end year] ))

Issue: when I calculate CAGR It is always 1.000
CAGR = (T_Measures[CAGR End Year Value]/T_Measures[CAGR Start Year Value])^(1/T_Measures[CAGR # of Years])-1

it seems not reflecting years changes

[Anonymous · Accepted solution]

Hi @ingyelmessiry ,

Please try modifying the formulas as below:

CAGR Start Year Value =
CALCULATE (SUM ('Raw Data'[Value] ),FILTERS(ALLSELECTED('Raw Data'),'Raw Data'[Year] = SELECTEDVALUE('Start Year Table'[Year])))

CAGR Eend Year Value =
CALCULATE(SUM ('Raw Data'[Value]),FILTERS(ALLSELECTED('Raw Data'),'Raw Data'[Year] = SELECTEDVALUE('End Year Table'[Year])))

 Since you did not provide the formula of [CAGR # of Years], please make sure it return the correct value you want.

Best Regards,

Jay