I currently have a measure which computes the year over year growth rate (see below).
I want to use these yearly growth rates to compute an index that starts at 10 and then multiplies the yearly growth rates sequentially. For example, the value for 2018 should be 10*(1+10.0%)*(1+18.2%)*(1+7.7%) = 14.
I know that ProductX function is supposed to achieve a similar result, however, it doesn't seem to work on calculated measures (I can't have the growth as a calculated column). Would be great if someone can suggest an alternative. I am also providing a link to the file for reference.
Solved! Go to Solution.
This is great thanks!
Just one follow up, I tried to compute the CAGR using the following formula, but I am not getting the right result:
CAGR = VAR BeginningDate = CALCULATE(MIN('Calendar'[Year]), ALLSELECTED('Calendar'[Year])) VAR EndingDate = CALCULATE(MAX('Calendar'[Year]), ALLSELECTED('Calendar'[Year])) VAR BeginningValue = CALCULATE([Measure], FILTER('Calendar', 'Calendar'[Year] = BeginningDate)) VAR EndingValue = CALCULATE([Measure], FILTER('Calendar', 'Calendar'[Year] = EndingDate)) VAR Years = (EndingDate - BeginningDate) RETURN IF(BeginningValue <= 0, BLANK(), (EndingValue / BeginningValue)^(1/Years) - 1)
Any idea why this formula is not working. Also the link to the file below:
Thanks a ton!
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.