CAGR Dax Measure

CAGR stands for Compound Annual Growth Rate.  It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis. 

The CAGR metric is calculated using the following formula:

If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out.

We will need to create several measures to calculate the individual pieces the CAGR formula.  Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it.  So, let’s break it down, we will need the following measures to calculate CAGR:

1. A measure to retrieve the First Year in the data set
2. A measure to retrieve the Last Year in the data set
3. A measure to calculate the Number of Years between the First and Last Years in the data set
4. A measure to aggregate the total sales in the data set
5. A measure to calculate the Sales in the First Year of the data set
6. A measure to calculate the Sales in the Last Year of the data set
7. Finally, a measure to calculate CAGR. 

The [First Year] measure is calculated as follows: 

=MIN('Calendar'[Year])

The [Last Year] measure is very similar:

=MAX('Calendar'[Year])

The [Number of Years] measure is simply calculated as the difference between the two years.  Unlike calculating dates, you need to resist the urge of adding one to this calculation as there is only one year’s growth between the first year and the next year, etc.

=[Last Year]-[First Year]

The sales aggregation measure is given by [Sales]:

=SUM('Sales Table'[Total Sales])

To calculate the [Sales in First Year] measure:

=CALCULATE(

[Sales],

                        FILTER(

                                    'Calendar','Calendar'[Year]=[First Year]

                        )

 

            )

There is therefore no surprise (hopefully!) that [Sales in Last Year] measure is calculated as follows:

=CALCULATE(

            [Sales],

                        FILTER(

                                    'Calendar','Calendar'[Year]=[Last Year]

                        )

 

            )

The [CAGR] measure is thus given by:

=([Sales in Last Year]/[Sales in First Year])^(1/[Number of Years])-1

This all seems to make sense.  That should be it: let’s create the PivotTable.  However, trying to plot our CAGR measure on a PivotTable we get the following message:

Something is not right!  Let’s plot the individual measures onto a PivotTable to see if we can figure it out:

It appears that our [First Year] and [Last Year] measures aren’t working as we intended, hence causing our [Number of Years] measure to return with ‘0’ every year.  This causes a division by zero error in our [CAGR] measure.

The first and last year measures return with the row’s first and last year.  This is because these measures are subject to the PivotTable row filters.  We are going to have to tweak the measures for them to consider the entire range of data, despite these row filters.

In this case we will have to use the ALL function.  You can read more about the ALL function here.

We have to modify the first and last year measures.  The [First Year] measure should be revised as follows:

=CALCULATE(

            MIN(

                        'Calendar'[Year]),

                        ALL(

                                    'Calendar'

                        )

            )

Similarly, the [Last Year] measure should be adapted too:

=CALCULATE(

            MAX(

                        'Calendar'[Year]),

                        ALL(

                                    'Calendar'

                        )

            )

Our [CAGR] measure now calculates on the PivotTable: 

Great, we finally have a working CAGR measure! But the measure only calculates the CAGR for the last year, what if we want the progressive CAGR for each year.

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]:

=MAX('Calendar'[Year])

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

=([Sales in Current Year]/[Sales in First Year])^(1/[Number of Years])-1

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:

<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-2735813199391136"

     crossorigin="anonymous"></script>