Cumulative return over measure

[xierwee]

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

[Iamnvt]

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]

[xierwee]

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

[Cmcmahan]

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]

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.

[xierwee]

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

[Cmcmahan]

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]))))

[xierwee]

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.

[Cmcmahan · Accepted solution]

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. 

[xierwee]

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

[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.