Measure Cumulated future Gain

Hey,

some things moved faster as assumed, and here you will find my solution that now considers also the portfolio if filtered. If the portfolio is not filtered, the calculation is also executed on the level of detail, this is to avoid using wrong interest rates and investments.

Here is an Excel file that helped me to cross check the measure, set the cell c2 to FALSE if the interest rate should not be applied in the first period of the investment.

What happens within in the measure CI is basically this

Create a table that is used in SUMX

• Iterate over the level of detail (the portfolio)
  • Find all investments for each level of detail
    • create the product of all interest rates for each investment using PRODUCTX
      • check if the interest rate has to be used in period the investment was take, yes interest rate no 1 
    • multiply the investment with the result of PRODUCTX
    • add the result to a virtual table in a column called Inv_Compound

The check I mentioned above looks like this

IF (
                                DATEDIFF (
                                    DATE ( YEAR ( vInvDate ), MONTH ( vInvDate ), 1 ),
                                    'Interest'[DateValue],
                                    MONTH
                                )
                                    = 0,
								// use just 1 if no interest rate should be applied in the period of the investment
                                1 + 'Interest'[InterestRate],
                                1 + 'Interest'[InterestRate]
                            )

Finally use SUMX(the table described above, Inv_Compound)

Hope this is what you were looking for