Using Table Iterators to calculate a future value

Often we use the Power of DAX statements to create complex calculations that provide tremendous insight into our data. But sometimes there are calculations that seem easy from an Excel point of view, but not possible in DAX. Excel provides the great possibility to create formulas referencing other cells or even ranges that reside in remote spreadsheets.

One kind of this calculation type is the iterative application of an interest rate over a timeseries to an investment where the amount of interest adds to the investment in the next period. This means, you have to use the concept of compound interest.

In the desktop forum of the Power BI Community I stumbled upon this particular question Cumulative time slicer calc that took my attention because it had some additional twists.

• Using a slicer to select a dynamic time frame and
• no interest should be applied to the month of the investment in the timeseries. Instead of using EARLIER(...) to gain access to values from a previous ROWCONTEXT I'm using variables (the ROWCONTEXT is one of the building blocks for the understanding of the intricate workings of DAX)

Here you can find a Power BI and an Excel file that cotain the source data for the Power BI model and the Excel solution.

The following screenshot shows what's there and the expected result:

Before I explain the solution in much more detail, I will briefly describe the business problem.

Over some periods of time various investments happen. To determine the value of These Investments in the subsequent periods it is not sufficient to sum the value of the investemnts. An interest has to be considered in future periods, this will help to determine the efficiency of the investment.

Basically what has to happen is shown in the Excel file shown in the row "The Formulas". From the question in the community I derived the following Business Rule.

The application of an interest rate starts in the next month.

I'm quite sure that there are as many possible Business rules as there are Businesses outside, but this does not affect the general pattern of the solution, but just the detail when the first interest rate has to be applied.

My assumptions for this example are:

• the granularity of the interest rates is a monthly one
• each investment uses the same timeseries
• the granularity for the investements is a daily one

To solve this problem the following DAX Statement can be used, it may look more complicated than it acutally is :-)

The DAX statement contains of two table-iterators SUMX(...) and PRODUCTX(...).

The outer iteration SUMX(...) sums values :-)

One value that is considered by SUMX(...) is calculated by the product of the investment multiplied with the result of PRODUCTX(...).

The first Parameter for SUMX(...) has to be a table. This table is created by a combination of GENERATE(...) and ADDCOLUMNS(...). Basically this table contains all investments for each member of the calendar table that have happened before or on the date of the filtered member of the calendar table (that member of the calendar table that determines the filter context).  ADDCOLUMNS(...) is used to calculate the column for the second parameter of SUMX(...). This 2nd Parameter also contains PRODUCTX(...). And suddenly there are nested table iterators, nested table iterators provide a solution for problems that would normally require some kind of recursion.

To consider the constraint of the Time Slicer as well as the "possibility" that one uses not just the 'Calendar'[Datevalue] column from the calendar table, some additional date operations are necessary. This becomes evident, if one uses Month as the granularity for the Calendar table, but uses the time slicer to select an April date that lies before the 7th of April (the April Investment). For this reason MAX('Calendar'[Datevalue]) is used, in combination with the LASTDATE(...) a variable thestopdate can be calculated. This variable is used to properly filter the Calendar and the Investment table.

Each row of the table that is calculated by GENERATE(...) is the input for the 2nd table iterator PRODUCTX(...). The first Parameter of PRODUCTX(...) is also a table, this table just contains one column, all the interest rates that have to be applied. To adhere to the Business Rule that the interest rate is first applied to the subsequent month for each single investment, within the 2nd Parameter of PRODUCTX(...), the xxpression, a simple IF(...) is used. This statement returns 1 if the member of the calendar table and the Investment date are in the same month, otherwise the interest rate is used. 1 is necessary because it does not affect the result of PROCUCTX(...).

Voila!