Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |