Skip to main content

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>

Comments

Popular posts from this blog

DAX Language - Data Analysis Expression

The DAX language was created specifically for the handling of data models, through the use of formulas and expressions. DAX is used in several Microsoft Products such as Microsoft Power BI, Microsoft Analysis Services and Microsoft Power Pivot for Excel. Below are the types of Dax functions  1. Aggregate  2. Date and time 3. Filter 4. Financial  5. Information  6. Logical 7. Maty and trig 8. Other 9. Parent and child 10. Relationship Management  11. Statistical  12. Table manipulation  13. Text 14. Time intelligence  From the above list of functions 3 types of functions are basic and commonly used, those are Aggregate , Logical and Date and time. Other important entities which are used with the above function are as follows  1. Operators  Example -  ( ), + , Not, &, =, < >, || 2. Statements  Define , Evaluate,  Order by, Return, Var 3. Data Types Binary, boolean,  Currency,  date time, decimal, integer,...

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...