Compound Growth using DAX

There are some things that are very easy to do in Excel that don’t seem to have an immediately obvious and simple solution in DAX.  One such problem is when you want to apply compound growth on an investment.  Let me make up an example so it is clear what I am talking about.

Simple Example – Fixed Growth Rate

Say you make an investment of $10,000 in a stock, and you want to forecast what that investment will be worth in 10 years time taking into account a projected annual growth rate.  Here is how you could do that in Excel.

image

Start with the value of the investment in year 0.  Then in all future years, multiply the value from the previous year by (1 + annual growth rate), or 1.08 in the example above.  What makes Excel such an easy tool to use is that you can write one formula for Year 1, then copy it down the page.  Each subsequent formula takes the result from the previous year and adds the growth for the current year to the previous calculation.

For the example above, you can use a simple single cell Excel formula to determine the value in a future year as follows, without having to calculate all the values for each year one at a time.

=FV(0.08,10,0,-10000)

The Function above applies an investment rate of 8%, over 10 years, making 0 extra payments, on an initial investment of $10,000.  This simple formula returns the same value of $21,589 in year 10; same as in the  Excel example above.

Harder Example – Variable Growth Rate

It is harder to write a single cell formula/function when you have a table of growth rates that vary over time.  But this harder example is still quite simple in a regular Excel workbook as shown below.

image

The formula is still essentially the same as the first Simple Excel example at the start of this article, but this time the calculation uses the forecasted rate for each year in the year by year calculation rather than a fixed annual rate.

Compound Growth Using DAX

Now back to the point of this article.  Compounding growth is very easy to do in Excel because you can write individual cell formulas (to do what ever you want), and each new formula can reference the answer from the previous formula as the starting point for the new formula. There is no such ability in the DAX language.  To solve such problems in DAX, you have to change the way you think and start to think about how to write a single formula that will work over an entire TABLE of data (or columns or multiple tables) – no cell by cell individual formulas are possible.

Below I will step you through the process of finding a solution to this problem.  As I often mention in my articles, it is the process that I believe is most important.  I seldom know how to answer a complex DAX problem when I start out (that’s the very definition of “complex”), and instead I follow a process to help me solve the problem.  Take a careful read below.  If you apply the same process when you write your formulas, you will be well on your way to becoming a DAX Superhero.

Step 1 – Load The Data

Because I am already in Excel, I loaded up the sample data from the example above into Power Pivot for Excel 2016.  The results would be the same if I solved the problem using Power BI Desktop.

image

Note that I have loaded up the year numbers as Integers instead of the text “Year 1, Year 2” etc.  This is an essential part of the solution to this problem, having an ordinal index indicating the order of the rows in the table.

Step 2 – Create a Pivot Table

This is one of my most important tips ever, and you would be well advised to take heed.  Before you do anything else, create a pivot table so you can “see” your data that lives in the underlying table.  This is particularly true for Excel folk (like me) as we are used to “seeing” stuff that we are working on.  It is orders of magnitude harder to write complex DAX formulas when you are not looking at a sample of your data on the screen.  If you are writing DAX in Power BI Desktop, I recommend you add a Matrix to the report and use that as the starting point.

Here is my pivot table, in tabular format, with subtotals turned off and the Year and Rate both placed on Rows.  I have written a simple measure Initial Investment = 10000 and placed that on Values in the pivot table.

image

Step 3 – Thinking Through the Problem

Now that you can “see” the data coming from the table in the pivot table above, it is easier to think through the problem.  Just as a reminder on the “challenge” at hand; we need to be able to write a single formula that will give the correct result for every row in the table – there is no opportunity to do cell by cell calculations and no opportunity to “point” to the previous result and include that inside any formula.

The second concept you need to get your head around is thinking of your data as “tables of data with filters applied (or not)”. There can only be a single formula that gives the correct results, and each result will be different due to the different filters that will be applied to the underlying data table.  With that in mind, let me show you what we need as the formula to calculate the future value as at any single year.

Year 0 = 10,000 * (1+ 0.00)

Year 1 = 10,000 * (1+ 0.00) * (1+0.08)

Year 2 = 10,000 * (1+ 0.00) * (1+0.08) * (1+0.07)

Year 3 = 10,000 * (1+ 0.00) * (1+0.08) * (1+0.07) * (1+0.06)

Year 4 = 10,000 * (1+ 0.00) * (1+0.08) * (1+0.07) * (1+0.06) * (1+0.06)

… and so on.

So you should identify the pattern above as “for each year, multiply the initial investment by (1 + growth rate) for each year up until the current year.  Referring to my tip above, is should be clear now that you need to filter the table of data so that you can access the current year and all the years prior to complete the calculation.  That is starting to look like a standard FILTER pattern to me.

Step 4 – Write an Interim Test Measure

The next thing to do is to write an interim test measure that “tests out” a filter pattern that keeps every row in the table up to and including the current row.  The final result of the test measure I wrote is shown below; it returns a count of how many rows are filtered for each row of the pivot table.

image

Test measures like this are great to help you work through the problem by breaking the problem down into pieces and allowing you to “see” the results as you proceed.  I can “see” above from my test measure that the filtered table is growing as it works down the pivot table.  Also I have used the VAR syntax to write my measure.  I am increasingly writing my measures this way as it makes the formulas almost self documenting and easier for others to read and understand.  By self documenting, I mean that the variable names are descriptive of what each piece of the formula does, and that makes the formula easier to understand.

Step 5 – Write the Final Measure

The final measure needs to multiply the initial investment by (1 + growth rate) for each row in the table after the filter has been applied.  That sounds like an iterating X function!  I know there is a SUMX, MINX, MAXX etc, so I went searching (using Intellisense) for a MULTIPLYX and TIMESX, but I lucked out.  I then remembered my DAX Quick Reference Guide that I prepared a few years ago – you can download a copy of it for free here.  A quick look in the PDF and I found what I was looking for – PRODUCTX. This function was released in Excel 2016 and Power BI Desktop, so no luck if you are using Excel 2013 or earlier.

Here is my final formula (reusing the guts of the interim test measure from above).

And here are the results of this measure in my pivot table (same answers as the Excel version at the top of the page Smile ).

image

The way the formula above works is

First the filter is applied to the table, so only the rows that should be included remain in the filter context. Then the PRODUCTX function iterates over this filtered table, one row at a time, multiplying (1+rate) for every row in the filtered table.  Finally this compound factor is multiplied by the initial investment.

Share?

Comments

  1. Hi Matt
    Great post.
    Firstly, here is a formula that works with Excel 2013.
    [DAX]
    Future value XL2013:=[Initial investment]*
    CALCULATE(
    EXP(SUMX(Rates,LN(1+Rates[Expected growth rate]))),
    FILTER(All(Rates),Rates[Year]<=MAX(Rates[Year])))
    [/DAX]
    It uses the natural log to turn the sum of values into the product of values.

    Secondly, I think the below formula for the will be more efficient for PBI. It takes advantage of the fact declaring a variable enables the filter function be avoided (my understanding is that the filter is an iterator function and therefore very slow).
    [DAX]Future value =
    VAR LatestYear=MAX(Rates[Year])
    RETURN
    [Initial investment]*
    CALCULATE(
    PRODUCTX(
    Rates,
    1+Rates[Expected growth rate]),
    Rates[Year]<=LatestYear)
    [/DAX]

    Interested to hear your thoughts on avoiding the filter function.

    • Nice Excel 2013 alternate! Regarding FILTER being inefficient because it is an iterator; this is not a blanket correct statement. Filter is an iterator, but under the hood there are engine optimisations that can and do achieve the outcomes more efficiently than is implied by the concept of iteration. I would generally advise against using a Filter over a 10 million row table, but it is fine to iterate over a low cardinality column in the same table. Also, your suggested formula doesn’t avoid FILTER at all.
      This
      CALCULATE(something, Table[column]=yada)

      is just syntax sugar for
      CALCULATE(something, FILTER(All(Table[Column]),Table[column]=yada))

      • Hi Andrew, Matt,
        Interestingly if you examine the xmSQL in DAX studio when using PRODUCTX you’ll see that the engine calls an internal function ‘LOGABS…’ with a precision guard column added. So the engine seems to use the same log/ln pattern as you Andrew.

  2. Thankyou Matt and thankyou Andrew. I can’t wait to try them. Excel is powerful. However, trying to replicate excel formulas in DAX can be torturous as you pointed out and as I’ve discovered trying to re-engineer Exel solutions.

  3. Good day, Have problem with Measure in place ” [Initial Investment] * CALCULATE (” (The value for ‘Initial Investment’ cannot be determined. Either ‘Initial Investment’ doesn’t exist, or there is no current row for a column named ‘Initial Investment’.) Only CALCULATE part works well.

  4. Very good Matt. Thanks for your input. Every time I learn more with your posts. You might as well have written it the following way, right?

    = VAR
    LatestYear = MAX (rates [Year])
    Var
    UnFilteredTable = ALL (rates)
    RETURN
    PRODUCTX (
    FILTER (UnFilteredTable; Rates [Year] < = LatestYear);
    (1 + rates [Expected Growth rate])
    ) * [Initial Investment]

    • Yes, there are many ways to write formula and your way is good. One thing to note is that if it were a multi table model, it may be necessary to use CALCULATE, but only if context transition is needed to complete the task (I’m not saying it would be needed, just sayin’).

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x