PowerPivot CAGR calculation

[bthomas]

Hi All!

I am trying to calculate the CAGR for 3 consecutive quarters in PowerPivot. I can't see to get the formula right. This is what I have tried.

Past 3 Qtr CAGR:=('Bookings Past 3 Quarters'[Prior Qtr -2 Bookings]/'Bookings Past 3 Quarters'[Prior Qtr Bookings])^(1/3)-1

Past 3 Qtr CAGR:=([Prior Qtr -2 Bookings]/[Prior Qtr Bookings])^(1/3)-1

The error I receive is that each field can't be determined in the current context.

Any help is greatly appreciated. I guess I am just having trouble figuring what order these formulas should be.

 

[Matt Allington]

I guess I am just having trouble figuring what order these formulas should be.

i don't think that is the issue. It's not possible to help you unless you provide information about the data model (tables, columns, measures, relationships)

 

[bthomas]

Hi!

I figured this one out.

The formula is:

3 QTR CAGR:=(DIVIDE([Sum of Column1],[Sum of Column2])^DIVIDE(1,3))-1

Sum of Column1 is a sum calculation of the prior year data (however many years back)
Sum of Column2 is a sum calculation of the current year data.
In the DIVIDE(1,3) change the 3 to the number of years.