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. The examples used in this post can be applied to NPV using the XNPV function.
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.
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.
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.
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.
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.
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.
FilteredRows = VAR LatestYear = MAX(Rates[Year]) VAR UnfilteredTable = ALL(Rates) RETURN COUNTROWS(FILTER(UnfilteredTable, Rates[Year] <= LatestYear))
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).
Future Value = VAR LatestYear = MAX(Rates[Year]) VAR UnfilteredTable = ALL(Rates) RETURN [Initial Investment] * CALCULATE( PRODUCTX(Rates, 1+Rates[Expected Growth Rate]), FILTER(UnfilteredTable, Rates[Year] <= LatestYear) )
And here are the results of this measure in my pivot table (same answers as the Excel version at the top of the page ).
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.
You can also read my article on User Specified Growth on an Investment using Power BI.