cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## ProductX not working with measure (alternative)

Hi everyone,

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.

Regards

Shashank

2 ACCEPTED SOLUTIONS
Super User

hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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:

https://www.dropbox.com/s/1tg4a8mj0dueypx/CAGR%20calcualtion.pbix?dl=0

Thanks a ton!

Regards

Shashank

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Great thanks again 🙂

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi please add the solution not only in the PBIX but also to a comment because over time you no longer have that PBIX available for download so the solution here isn't available rendering this post worthless.