Forum Discussion

xierwee's avatar
xierwee
Helper I
7 years ago
Solved

Cumulative return over measure

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.
 
datefundreturnweight
Thursday, November 30, 201710.011337440.053968056
Thursday, November 30, 201720.007028070.04245025
Thursday, November 30, 201730.00530.019089831
Thursday, November 30, 201740.01561190.035882437
Thursday, November 30, 20175-0.024197350.056629009
Thursday, November 30, 20176-0.006868860.026112976
Sunday, December 31, 201770.013733750.044470247
Sunday, December 31, 201780.0435761920.031402827
Sunday, December 31, 201710.001457220.054592163
Sunday, December 31, 201720.034274740.04286166
Sunday, December 31, 201730.010156530.019194537
Sunday, December 31, 201740.006559880.036444948
Sunday, December 31, 20175-0.001476590.055268904
Sunday, December 31, 20176-0.062520990.025938378
Wednesday, January 31, 201870.041953840.046180235
Wednesday, January 31, 201880.035173980.033591548
Wednesday, January 31, 201810.03529470.049886267
Wednesday, January 31, 201820.012883010.045347082
Wednesday, January 31, 201830.013616720.019874837
Wednesday, January 31, 201840.016100360.037566834
Wednesday, January 31, 201850.0260.056568703
Wednesday, January 31, 201860.026674160.024925369
Wednesday, February 28, 201870.014733890.046931471
Wednesday, February 28, 201880.015553120.033915857
Wednesday, February 28, 20181-0.047580110.050346059
Wednesday, February 28, 201820.007277130.044755501
Wednesday, February 28, 20183-0.004163340.019648835
Wednesday, February 28, 201840.001217240.037215983
Wednesday, February 28, 201850.0139910.056608682
Wednesday, February 28, 201860.003786320.024959378
  • 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

  • Iamnvt's avatar
    Iamnvt
    Continued Contributor

    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's avatar
      xierwee
      Helper I

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

       

      • Cmcmahan's avatar
        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.