Power Pivot Principles: How to Create the CAGR Measure, Part 3

Welcome back to our Power Pivot blog.  We continue on our CAGR series.  Today, we shall look into making our CAGR measure more dynamic and discuss additional improvements to the measures.

Last week we left off at a functioning CAGR measure (you can read last week’s blog here).  However, it only calculated the CAGR value for the final year in our data.  What if we wanted the CAGR measure to be dynamic and calculate the CAGR value for each year we have on our PivotTable?

The reason why our CAGR formula is not calculating dynamically is because we are using the [Last Year] measure, which is currently set to retrieve the last year in our data set.  To avoid confusion, we are going to create a measure that retrieves the current year called [Current Year]:

Now we modify our [CAGR] measure to use the [Current Year] measure:

However, attempting to put our CAGR measure in the Pivot Table will result in this error:

This is because the number of years for the 2018 period is zero, and we can’t divide by zero. In this scenario, we should use the DIVIDE function to create our [CAGR Dynamic] new-and-improved measure. You can read more about the DIVIDE function here:

=DIVIDE(

[Sales in Current Year],[Sales in First Year]

)

^DIVIDE(

1,[Number of Years]

)-1

The DIVIDE function manages our division by zero problem by returning with BLANK() when the denominator is zero. Therefore, this gives us the dynamic CAGR calculation we require:

That’s it for this week, happy Pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.