I wrote an article earlier this year that showed how you can take a table of future growth rates (different growth rates each year) and then calculate the future value of an investment using DAX. I had a follow up question from Steve asking if it is possible to allow the user to select a growth rate from a slicer (same growth rate each year) and calculate the future value. I thought that would answer that question in this blog. The concepts in this article can be applied to NPV using the XNPV function.
What If Parameters in Power BI
I am going to use the What If Parameter feature in Power BI as part of the solution, but it could also be solved by simply adding data to tables using Home\Enter Data in Power BI or by using Power Pivot\Add to Data Model in Excel.
Load the data
First I created the 2 tables I want to use. The first is a growth rate table. I used the New Parameter button as shown below and added a list of possible growth rates as shown in 3 below.
After clicking OK, Power BI does a few things.
- It creates a new table (shown as 1). This new table is generated via a DAX table function (2 below).
- The new table has a single column of values (shown as 3 below).
- The table contains a single measure that returns the currently selected value of the growth rate (4 below).
Here is the measure that was auto-generated by the What If Parameter button.
Growth Value = SELECTEDVALUE(Growth[Growth])
And Power BI adds a slicer the the report that is connected to the table as shown below.
Next I created a list of years, but instead of using the What If Parameter feature, I decided to simply write a new DAX table function using the GENERATESERIES table function shown above.
I then added this new column of values into a Table onto the report canvas. I needed to change the default summarization method of this new column as I don’t want to add up the years, but instead use the years as rows on a table. I made the change as shown below.
I then wrote a simple “Starting Value” measure and set it to 100,000. After the above setup, this is what I had on my report canvas.
- A slicer connected to my disconnected rates table (created by the What If Parameters button).
- A table connected to my Year table (table created using GENERATESERIES)
- A card that shows the value of the Starting Value measure
- A card that shows the current selected value in the Growth slicer.
Note, even though I have 2 tables loaded, there is no relationship between these tables – they are “disconnected tables”.
DAX formula to Forecast Future Value
The DAX formula is very similar to the one I wrote in the previous article. Here is the formula I wrote to solve this problem in this article.
Future Value = VAR LatestYear = MAX(Years[Year]) VAR UnFilteredTable = ALL(Years) RETURN PRODUCTX( FILTER(UnFilteredTable, Years[Year] <= LatestYear), (1 + [Growth Value]) ) * [Starting Value]
The way this formula works is as follows.
- Lines 2 through 4 define 2 variables. I could have used SELECTEDVALUE(Years[Years]) in line 3 and I would get the same result.
- Starting from line 7, the PRODUCTX function iterates through a virtual table (line 8). This virtual table contains all of the years up to the last selected year. When the years are in a visual (such as a matrix or a table), the virtual table will include all years up to and including the current year in the row. If there are no years selected, the virtual table will include all years in the the table.
- Line 9 is where the actual calculation occurs. This line of code effectively returns (1+growth)^n where n=the number of years.
- Finally the compound growth from line 9 is multiplied by the starting value in line 11.
Here is the result
Note that I renamed the bottom right card visual. This visual contains the Future Value measure (renamed to be “Future Value in 10 Years” in the visual. Because there is no “filter” on the year, it calculates the future value over all of the data in the Years table (10 years in this case).
Here is my sample workbook if you want to take a look. future value fixed growth rate