Forum Discussion
Calculate CAGR based on dynamic range
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
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
1 Reply
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