Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Solved! Go to 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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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
Via a PM is fine.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Can you share some sample data of your tables with expected outcomes. Should be a quick fix.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Check out the November 2023 Power BI update to learn about new features.