Calculated Time weigthed return in Power BI

Question

Im trying to calculate the Time Weigthed Return for a portfolio of stocks. The formula is:

I have the following data:

Im calculate the TWR (time weigthed return) in Power Bi as:

TWR = productx(tabel1;TWR denom/yield+1)

The grey and blue marked/selected fields are individual single stock. Here you see the TWR for the grey stock is = 0,030561631 and for the blue TWR = 0,012208719 which is correct for the period from 09.03.19 to 13.03.19.

My problem is, when im trying to calculate the TWR for a portfolio of the two stocks, it takes the product og every row. In the orange field I have calculated the correct result in excel. But in Power BI it takes the product of the grey and blue stocks TWR: (0,0305661631 * 0,012208719) = 0,03143468 which is incorrect.

I want to sum(yield for both stocks)/sum(TWRDenominator for both stocks) for both stocks every single date, such that I not end up with two rows (one for each stock) but instead a common number every date for the portfolio.

I have calculated the column TWR denom/yield -1 in a measure like this: twr denom/yield-1 = CALCULATE(1+sumx(tabel1;tabel1(yield)/sumx(tabel1;tabel1[TwrDenominator])))

How can I solved this problem?

Thank you in advance!

OscarLar OscarLar · Accepted Answer · 2019-03-20T09:02:56

This is one solution to your question but it assumes the data is in the following format:

[Date] | [Stock] | [TWR] | [Yield]
-----------------------------------
[d1]   | X       | 12355 | 236
[d1]   | y       | 23541 | 36
[d2] ... etc.

I.e. date is not a unique value in the table, though date-stock name will be.

Then you can create a new calculated table using the following code:

Portfolio_101 = 
CalculateTable(
    Summarize(
        DataTable;
        DataTable[Date];
        "Yield_over_TWR"; Sum(DataTable[Yield])/Sum(DataTable[TWR_den])+1
    );
    Datatable[Stock] in {"Stock_Name_1"; "Stock_Name_2"}
)

Then in the new Portfolio_101 create a measure:

Return_101 = 
Productx(
    Portfolio_101;
    Portfolio_101[Yield_over_TWR]
)-1

If using your data I en up with the following table, I have created three calculated tables, one fore each stock and a third (Portfolio_103) with the two combined. In addition I have a calendar table which has a 1:1 relationship between all Portfolio tables.

Calculated TWR

Hope this helps, otherwise let me know where I've misunderstood you.

Cheers,

Oscar

Calculated Time weigthed return in Power BI

1 Answers