- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative return over measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Cumulative return over measure

07-23-2019
06:09 AM

The whole idea is to calculate the cumulative return given any two or three funds selected by slicer. I want to calculate the cumulative return based on returns computed in a measure. The return is calculated through

norm_return = sumx(perf, divide(sum(perf[weight]),calculate(sum(perf[weight]), ALLSELECTED(perf[fund])))*perf[return])

perf is the following table.

date | fund | return | weight |

Thursday, November 30, 2017 | 1 | 0.01133744 | 0.053968056 |

Thursday, November 30, 2017 | 2 | 0.00702807 | 0.04245025 |

Thursday, November 30, 2017 | 3 | 0.0053 | 0.019089831 |

Thursday, November 30, 2017 | 4 | 0.0156119 | 0.035882437 |

Thursday, November 30, 2017 | 5 | -0.02419735 | 0.056629009 |

Thursday, November 30, 2017 | 6 | -0.00686886 | 0.026112976 |

Sunday, December 31, 2017 | 7 | 0.01373375 | 0.044470247 |

Sunday, December 31, 2017 | 8 | 0.043576192 | 0.031402827 |

Sunday, December 31, 2017 | 1 | 0.00145722 | 0.054592163 |

Sunday, December 31, 2017 | 2 | 0.03427474 | 0.04286166 |

Sunday, December 31, 2017 | 3 | 0.01015653 | 0.019194537 |

Sunday, December 31, 2017 | 4 | 0.00655988 | 0.036444948 |

Sunday, December 31, 2017 | 5 | -0.00147659 | 0.055268904 |

Sunday, December 31, 2017 | 6 | -0.06252099 | 0.025938378 |

Wednesday, January 31, 2018 | 7 | 0.04195384 | 0.046180235 |

Wednesday, January 31, 2018 | 8 | 0.03517398 | 0.033591548 |

Wednesday, January 31, 2018 | 1 | 0.0352947 | 0.049886267 |

Wednesday, January 31, 2018 | 2 | 0.01288301 | 0.045347082 |

Wednesday, January 31, 2018 | 3 | 0.01361672 | 0.019874837 |

Wednesday, January 31, 2018 | 4 | 0.01610036 | 0.037566834 |

Wednesday, January 31, 2018 | 5 | 0.026 | 0.056568703 |

Wednesday, January 31, 2018 | 6 | 0.02667416 | 0.024925369 |

Wednesday, February 28, 2018 | 7 | 0.01473389 | 0.046931471 |

Wednesday, February 28, 2018 | 8 | 0.01555312 | 0.033915857 |

Wednesday, February 28, 2018 | 1 | -0.04758011 | 0.050346059 |

Wednesday, February 28, 2018 | 2 | 0.00727713 | 0.044755501 |

Wednesday, February 28, 2018 | 3 | -0.00416334 | 0.019648835 |

Wednesday, February 28, 2018 | 4 | 0.00121724 | 0.037215983 |

Wednesday, February 28, 2018 | 5 | 0.013991 | 0.056608682 |

Wednesday, February 28, 2018 | 6 | 0.00378632 | 0.024959378 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-25-2019
11:39 AM

Sure. So first things first, lets fix your original norm_return measure:

norm_return = SUMX(perf, DIVIDE([weight],SUM([weight])) * [return] )

Cumulative normalized return would then be calculated like this:

cumul_norm_return = PRODUCTX(SUMMARIZE(FILTER(ALLSELECTED(perf), [date]<=MAX([date])),[date],"NormReturn", 1+[norm_return]), [NormReturn])-1

So now we can get into some of the more interesting quirks of this solution. The first one being that if a user only selects Jan & Feb, the cumulative return only starts cumulating with the data from those months. It's like Nov&Dec don't even exist, and the portfolio started fresh in Jan. This may be the behavior you want, that's up to you.

One of the big downsides is that this measure groups the calculations by date. If you add [fund] to the legend of a bar graph, the graph will display each fund as having a cumulative return equal to the entire portfolio's cumulative return. This seemed wrong, so I spent some time fixing it, and came up with two solutions. One that uses SUMMARIZE, and one that doesn't. As far as I can tell, they both give the same result, and I'm not sure which one has better performance, so I'll include them both here:

cumul_norm_return = PRODUCTX(SUMMARIZE(FILTER(ALLEXCEPT(perf,perf[fund]), [date]<=MAX([date])),[date],"NormReturn", 1+[norm_return]), [NormReturn])-1

cumul_norm_return_summarizefree = CALCULATE( PRODUCTX(ALLSELECTED(perf[date]), (1+[norm_return]))-1, FILTER(ALLEXCEPT(perf, perf[fund]), perf[date]<=MAX(perf[date])))

These both give correct answers whether you lump the funds together in a visual or split them out individually. However, we have re-introduced the problem (which may or may not be a problem for you) where even if you have filtered out Nov2017, it will still use that data in the cumulative return. This is due to the ALLEXCEPT removing all filters except the ones on [fund]. So we end up using all dates that are less than the current date, even if the slicers are removing them.

I spent more time than I'd like to admit learning how to combine these two versions. But in the end, I got to a single measure that will correctly give me the cumulative return of a single fund, or entire portfolio, as well as only using dates I have selected via filters to calculate that cumulative return.

cumul_norm_return_summarizefree = CALCULATE( PRODUCTX( ALLSELECTED(perf[date]), (1+[norm_return]))-1, FILTER( ALLSELECTED(perf), perf[date]<=MAX([date]) && perf[fund] IN FILTERS(perf[fund]) ) )

The trick was using ALLSELECTED to use only the dates shown in the visual, but also using FILTERS to re-apply the current filters on fund.

This was a fun project. I've never used the FILTERS function before This is what I came up with in case you have questions or want to play with it.

11 REPLIES 11

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
06:13 AM

hi,

you can try the following:

norm_return = SUMX ( FILTER ( ALL ( perf ), perf[date] <= MAX ( perf[date] ) ), DIVIDE ( SUM ( perf[weight] ), CALCULATE ( SUM ( perf[weight] ), ALLSELECTED ( perf[fund] ) ) ) * perf[return]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
06:32 AM

thanks for your input. For some reasons, the result is too high. This cumulative return should lie between 1-2.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
08:37 AM

Should this cumulative total reset when the year changes? It seems low enough early on, but if you're adding 0-1 points every month, it adds up over the years.

Right now, it seems your measure calculates values for a given date like this:

- SUM
__all__the weights with a date prior to the currently selected date- This ignores any other filters, including using weights from all the different funds

- Divide the above sum by the sum of ALL weights in the visual, from all funds.
- Multiply the above result by the current return for the given date and given fund

This seems to be mixing data from different funds very often, but maybe you do want some sort of overall portfolio view.

I've been trying to understand this problem using the sample data you provided, and slimming it down to just use the Nov 2017 data to keep the dataset small. I split out the graphs by fund, just to see what has happening, and got these graphs.

So with no prior data, I would assume a cumulative return would match the actual return, but that's not the case with the current measure. I tried to figure out what numbers the expression was summing/dividing/multiplying to get a single result, and I was completely unable to figure out how the values are calculating using @Iamnvt 's solution.

So to move forward with this, can we start with your simple data set?

If you wanted the norm_return for Nov 2017 of your sample data, what would the math look like if you wrote it out on paper? If that's too many numbers, how should the norm_return be calculated for Fund 1 in Nov 2017?

Then for the cumulative norm_return, in Dec2017 would that be (Nov2017's norm_return) + (Dec2017's norm_return)? Or would that calculate differently than a normal cumulative total?

My apologies for all the questions, but I just don't understand the math that's supposed to be happening here, which makes it difficult to give you a specific answer.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
09:10 AM

Hi @Cmcmahan,

thanks for your attention. My measure is calculating this:

1. sum up the weights of funds selected by slicer.

2. divide the weights of the selected funds by the total weights from step 1

the above two steps are normalizing the weights.

3. multiply the normalized weights with their returns seperately, in this way, we can get the portfolio return.

I checked, until this step the results are correct.

to calculate the cumulative return, one doesn't need to reset every year. actually it should be accumulated.

In excel, the formula is product((year1.month1 return+1):(yearn.monthn return+1)) -1.

The last step, I cannot replicate the result in DAX. 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
10:36 AM

I tried to make something that looked more like your excel formula, and I came out with this:

cumulative_norm_returnPRODUCT = CALCULATE(PRODUCTX( SUMMARIZE(perf, perf[date], "return", [norm_return]+1), [return]), FILTER(ALLEXCEPT(perf, perf[fund]), perf[date]<=MAX(perf[date])))-1

No idea if that helps, but it at least is multiplying the terms and treating them like percentages again instead of summing them.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-24-2019
02:05 AM

thank you for your help. But unfortunately the result doesn't seem correct.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-24-2019
10:11 AM

So would it be possible using some sample data to share how you expect the cumulative return to be calculated with specific values? Stating "that doesn't seem right" without any indication of what's wrong is useless for ultimately fixing it.

Index | Date | Fund | Return | Weight |

1 | Thursday, November 30, 2017 | 1 | 0.01133744 | 0.053968056 |

2 | Thursday, November 30, 2017 | 3 | 0.0053 | 0.019089831 |

3 | Thursday, November 30, 2017 | 5 | -0.02419735 | 0.056629009 |

4 | Sunday, December 31, 2017 | 1 | 0.00145722 | 0.054592163 |

5 | Sunday, December 31, 2017 | 3 | 0.01015653 | 0.019194537 |

6 | Sunday, December 31, 2017 | 5 | -0.00147659 | 0.055268904 |

If you used __just__ this subset of your sample data, could you provide expected results for Nov2017 and Dec2017 cumulative return? It would be even more helpful (though obnoxious, I know) to write out the entire formula, plugging in each actual number from the table and sharing that.

I've added an index so you can refer to values that way instead of typing out all 8 decimal places. For example, the calculation to get the normalized return for Nov 2017 would look like:

( SUM(Weight[1..3]) / Weight[1] * Return[1]) +

( SUM(Weight[1..3]) / Weight[2] * Return[2]) +

( SUM(Weight[1..3]) / Weight[3] * Return[3])

The actual values being used in the calculation with the above data would be:

(.053968056+.019089831+.056629009)/.05396806*.01133744 +

(.053968056+.019089831+.056629009)/.01908983*.00530 +

(.053968056+.019089831+.056629009)/.05662901*-0.02419735

=0.0078351

If you could do the same thing with the cumulative return for Nov2017 and Dec2017 so I can see how the math progresses and changes between months, that would be a **huge** help in figuring out how to get your desired solution!

PS: As a side note, I still think your original [norm_return] measure is calculating incorrectly. It took me a VERY long time to figure out the actual numbers used in a very small normalized return calculation. With just these 3 entries in November, not only does Fund 5 have the largest weight of the three, the magnitude is greater than the other two combined, and is negative.

I'm no expert on how a normalized return should be calculated, but it seems that if the biggest chunk has a larger negative return and is weighted more, then the normalized return for the entire month of November should be negative. If you remove the SUM within your SUMX on your norm_return measure, the result makes more sense to me as an aggregation:

norm_return = sumx(perf2, perf2[return]*divide(sum(perf2[weight]),calculate(sum(perf2[weight]), ALLSELECTED(perf2[fund]))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-25-2019
03:01 AM

thanks very much.

here is what I expected how to calculate the return.

for Nov. Normlized weights should be

w_fund1 = 0.053968056/sum(0.053968056+0.019089831+0.056629009)

w_fund2 = 0.019089831/sum(0.053968056+0.019089831+0.056629009)

w_fund3 = 0.056629009/sum(0.053968056+0.019089831+0.056629009)

return of the portfolio for Nov. would be: 0.01133744* w_fund1 +0.0053*w_fund2+-0.02419735*w_fund3 = -0.005067869

Same calculation for Dec, portfolio return in Dec would be: 0.001494652

In the end cumulative return should be (1+(-0.005067869))*(1+0.001494652)-1

I want to make the calculation dynamically based on slicer of time and funds.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-25-2019
11:39 AM

Sure. So first things first, lets fix your original norm_return measure:

norm_return = SUMX(perf, DIVIDE([weight],SUM([weight])) * [return] )

Cumulative normalized return would then be calculated like this:

cumul_norm_return = PRODUCTX(SUMMARIZE(FILTER(ALLSELECTED(perf), [date]<=MAX([date])),[date],"NormReturn", 1+[norm_return]), [NormReturn])-1

So now we can get into some of the more interesting quirks of this solution. The first one being that if a user only selects Jan & Feb, the cumulative return only starts cumulating with the data from those months. It's like Nov&Dec don't even exist, and the portfolio started fresh in Jan. This may be the behavior you want, that's up to you.

One of the big downsides is that this measure groups the calculations by date. If you add [fund] to the legend of a bar graph, the graph will display each fund as having a cumulative return equal to the entire portfolio's cumulative return. This seemed wrong, so I spent some time fixing it, and came up with two solutions. One that uses SUMMARIZE, and one that doesn't. As far as I can tell, they both give the same result, and I'm not sure which one has better performance, so I'll include them both here:

cumul_norm_return = PRODUCTX(SUMMARIZE(FILTER(ALLEXCEPT(perf,perf[fund]), [date]<=MAX([date])),[date],"NormReturn", 1+[norm_return]), [NormReturn])-1

cumul_norm_return_summarizefree = CALCULATE( PRODUCTX(ALLSELECTED(perf[date]), (1+[norm_return]))-1, FILTER(ALLEXCEPT(perf, perf[fund]), perf[date]<=MAX(perf[date])))

These both give correct answers whether you lump the funds together in a visual or split them out individually. However, we have re-introduced the problem (which may or may not be a problem for you) where even if you have filtered out Nov2017, it will still use that data in the cumulative return. This is due to the ALLEXCEPT removing all filters except the ones on [fund]. So we end up using all dates that are less than the current date, even if the slicers are removing them.

I spent more time than I'd like to admit learning how to combine these two versions. But in the end, I got to a single measure that will correctly give me the cumulative return of a single fund, or entire portfolio, as well as only using dates I have selected via filters to calculate that cumulative return.

cumul_norm_return_summarizefree = CALCULATE( PRODUCTX( ALLSELECTED(perf[date]), (1+[norm_return]))-1, FILTER( ALLSELECTED(perf), perf[date]<=MAX([date]) && perf[fund] IN FILTERS(perf[fund]) ) )

The trick was using ALLSELECTED to use only the dates shown in the visual, but also using FILTERS to re-apply the current filters on fund.

This was a fun project. I've never used the FILTERS function before This is what I came up with in case you have questions or want to play with it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-29-2019
03:06 AM

@Cmcmahan: Thank you very much for your help. The last solution works perfectly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2019
10:28 AM

Seems odd, but if it gets you the result we're looking for, we'll assume the math works out for [norm_return]

If you want a straight cumulative total of [norm_return], you should be able to use a measure like this:

cumulative_norm_return = CALCULATE(SUMX( SUMMARIZE(perf, perf[date], "normalized return", [norm_return]), [normalized return]), FILTER(ALLEXCEPT(perf, perf[fund]), perf[date]<=MAX(perf[date])))

This still looks like it would grow at a similar rate to the previous calculation which was already too high for you, but the cumulative total for any given date is the sum of [norm_return] for that column and every column preceeding.

If you don't want a true cumulative total of past [norm_return]s, then I'll need more info in how you want to actually calculate it, since I don't have experience with Excel formulas like the one you provided.

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

99 | |

95 | |

88 | |

70 |

Top Kudoed Authors

User | Count |
---|---|

165 | |

130 | |

129 | |

102 | |

98 |