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

    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.