Compounded Annual Growth Rate (CAGR) in MicroStrategy

The idea behind using a compounded annual growth rate is to smooth out the rate of change that something sees period over period for a successive number of periods.  Out of the box in MicroStrategy there is no function that creates this value automatically, so if a CAGR calculation is needed, either a custom metric or a custom subtotal is required.

The calculation for a compounded annual growth rate looks like this:

formula

In the CAGR equation we divide the ending value by the beginning value, raise it by a power — in this case the number of years that compounded — and subtract the 1 from it to get a percentage value.

…and in Excel, the formula to do this will use the caret to denote that we are raising the proportion of change by a power equivalent to the number of periods that the data spans.

CAGRinExcel

To do this in MicroStrategy, the best place to apply this logic is in a subtotal.  For the uninitiated, the ability to create new subtotals is very powerful, and often the best way to shoehorn a report look and feel into a grid.

To create a new subtotal for CAGR in MicroStrategy we have to specify the exponent using the power() function, and the At sign is going to be the

subtotal

Here’s the code for the calculation:

1
(POWER((LAST(x) {@}  / FIRST(x) {@} ), (1 / COUNT(YEAR) {@} )) - 1)

The second to last step in the process is to add the subtotal to a report.  Since this subtotal is for a year, adding the CAGR means that it should be the last column on the grid report.  To make the CAGR subtotal visible as an option, it needs to be added to the list of available subtotals for the metric.  In this example we are working with a revenue calculation so we need to move the new subtotal as being available to the revenue metric.

addsubtotal

The final piece once there is a report that contains both the Year attribute and the Revenue calculation is to add the subtotal at the right level.  For this, go to the advanced subtotals section of the grid, and put the CAGR subtotal across the Year level.

acrosslevel

The final output should look something like this:

report

Note that the calculation used in this subtotal is for year, but the concept can apply to any time period.  With the same implementation, but using a different attribute (month) the calculation will only differ by the attribute name:

1
(Power((Last(x) {@}  / First(x) {@} ), (1 / Count(Month) {@} )) - 1)