Forum Discussion
Helper ICumulative return over measure
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.
Continued Contributorhi,
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]
Helper Ithanks for your input. For some reasons, the result is too high. This cumulative return should lie between 1-2.
- Cmcmahan
Resident Rockstar
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.
- xierwee
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. :(
- Cmcmahan
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.
- SUM all the weights with a date prior to the currently selected date