Formula for total calculation

[TeunVerhagen]

Hi all,

I am having an issue, tried a lot, but cannot get a solution. This is the situation:

There are two tables.

- One table with Investments and periods, columns: InvestmentID - Start date - End Date - Start value - End value

- One table with Cashflow looking like this: InvestmentID - Cashflow type - Value - Start Date - End Date

These two tables are linked so that I can create the following formula:

(SUM(end value) - SUM(start value) - (Cashflow values) / SUM(start value) + SUM(Weighted Cashflow values)

So far so good. This formula calculates the return per investment per period. The next step is linking de returns to get a time weighted return. This is done by adding 1 to the returns, take the product of returns +1, and lastly minus 1. I made this formula: PRODUCTX(Table, (Return+1))-1.

This also works fine but only on investment level. When I want to calculate the time weighted return of multiple investments it goes wrong because my formula takes the product of all the returns in stead of the subtotals. Example:

Investment 1

Q1: 2%

Q2: 3%

Q3: 1.5%

Q4: 5%

Time weighted return = (1.02 * 1.03 * 1.015 * 1.05) -1

Investment 2

Q1: 1.5%

Q2: 2%

Q3: 4%

Q4: 3%

Time weighted return = (1.015 * 1.02 * 1.04 * 1.03) -1

But when I want to calculate the time weighted return of both investments this is what needs to happen:

Result when I create a table with a filter on 2 investments: (Calculation of return works fine for multiple investments)

Investment 1&2

Q1: 3%

Q2: 1.6%

Q3: 2.8%

Q4: 3.8%

Time weighted return: (1.03 * 1.016 * 1.028 * 1.038) -1 

But when I use this formula: PRODUCTX(Table, (Return+1))-1, PowerBI multiplies all individual returns of investment 1 and 2. What is the result? (1.02 * 1.03 * 1.015 * 1.05 * 1.015 * 1.02 * 1.04 * 1.03) -1. This is the wrong result.

Does anyone have an idea how to solve this? Below I posted a screenshot to visualize things; the outcome of the desired formula should be: (1.0278 * 1.0158 * 1.0167 * 1.1053) -1 = 17.32%

Can someone help me? I am stuck now for 3 days on this issue.

[stevedep · Accepted solution]

Hi @TeunVerhagen ,

In this case its required to set the right level of aggregation in the productx command. Productx takes a table as a parameter. By giving a table on the level of period you will get the right results.

Modified Dietz = PRODUCTX(SUMMARIZE('NAV Dietz Table','NAV Dietz Table'[NAV Date Start],'NAV Dietz Table'[NAV Date End]),ROUND([Dietz]+1,2))-1

Kind regards, Steve. 

[TeunVerhagen]

Thanks for your reply!

I have made an Excel with sample data and a PowerBI file but I can't share it in this forum for some reason.

Is there another way to send you the files?

Kind regards,

Teun

[stevedep]

Via a PM is fine.

[stevedep]

Can you share some sample data of your tables with expected outcomes. Should be a quick fix.