Measure Cumulated future Gain

[fjjohann]

Hello guys,

I need to Create a Measure (VLR TOTAL) according to the image below:

[TomMartens · Accepted solution]

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

[fjjohann]

Thank you very much!

Worked perfectly.

I will study deeply the logic that compose this measure.

Thank you so much!

[TomMartens]

Hey, feel free to ask. And don't hesitate to give a kudo to my answer, if you think the answer deserves it 😉 Glad it works the way

[v-ljerr-msft]

Hi @fjjohann,

Following from other replies, you basically need to calculate the cumulative product of your 'growth factors'.

Gerhard Brueckl's blog (link here) had a method using summing logarithms, then mentioned that you can now use PRODUCTX.

---

According to your description above, you may need to do a Recursive Calculations in Power BI using DAX. Here is a similar thread, could you go to check if it helps in your scenario? 

Regards

[fjjohann]

Thank you very much for your reply @v-ljerr-msft.

I analyzed the link and a following formula served me in parts:

Sales ForeCast PRODUCTX: = IF (ISBLANK ([Sales]), CALCULATE (
PRODUCTX (
VALUES ('Date'),
[MultiplyBy]),
DATESBETWEEN ('Date' [DateValue], BLANK (), MAX ('Data' [DateValue]))
),
[Sales]
)

I need to consider a monthly deposit amount to be aggregated into the calculation.

I could not find a way to do that.

You would know?

[TomMartens]

Hey,

maybe this can be of some helps, I "translate" your monthly deposit into my "investments" that I described some days ago:

https://docs.com/minceddata/3687/dax-using-table-iterators-to-calculate-a-future?c=B13yYP

Hope this is what you are looking for

[fjjohann]

Thank you very much @TomMartens

Your measurements work very well.

However, I am not able to take into account the following:

I have a column named Portfolio in both the InterestRates Table and the Investment Table.

I can not put Porfolio as a panel filter. The CI measure calculates all table values independent of the filter.

[TomMartens]

Hey,

can you please prepare an EXCEL, that contain a minimum timeseries to show your requirement. And share the link to the Excel file, preferrable on OneDrive or Dropbox, because I've encountered problems with other fileshares.

Cheers

[fjjohann]

https://1drv.ms/x/s!AnauFj-Y0kAbhQCACc8OQD2ro3k7

[TomMartens]

Hey,

thanks for sharing some data, I will have a closer look in the evening today, but I'm quite busy the next days.

So it can take until the weekend

Can you please add the expected result to your excel file.

And one question: I assume that the interestrate is a monthly one?

Cheers