ProductX not working with measure (alternative)

[sgupta22]

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.

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

Regards

Shashank

@Ashish_Mathur 

[Ashish_Mathur · Accepted solution]

hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

hi,

You may download my PBI file from here.

Hope this helps.

[sgupta22]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sgupta22]

Great thanks again 🙂

[Ashish_Mathur]

You are welcome.