Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating Cumulative Returns from Daily Returns tables

I work in financial services and am new to Power BI (from Qlik).  All of our data is in the form of a daily date column and daily returns for portfolios, benchmarks, stocks, etc.  I would like to u...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could try

Cumul Total =
var minDate = MIN('Date'[Date])
var maxDate = MAX('Date'[Date])
return CALCULATE( SUM('Table'[Returns]), REMOVEFILTERS('Date'), 'Date'[Date] >= minDate && 'Date'[Date] <= maxDate)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    johnt75 

    Thanks for getting back to me.

    I just tried your expression but that gives me the same thing as the daily returns on the chart, it does not accumulate through time.

     

    Thanks for the help though.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      By way of help, the expression I used in Qlik (and Tableau) was: 

      exp(RangeSum(Above(log(1+([daily return]/100)), 0, RowNo()))) - 1

      I hope that helps. I need the equivalence in DAX.

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

      Replace the MIN('Date'[Date]) with CALCULATE( MIN('Date'[Date]), ALLSELECTED('Date'))

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        This is what I have now:

         

        Cumm Total = var minDate = CALCULATE( MIN('Date'[Date]), ALLSELECTED('Date'))
        var maxDate = MAX('Date'[Date])
        return CALCULATE( SUM('MH-ALL'[Dividend Yield]), REMOVEFILTERS('Date'), 'Date'[Date] >= minDate && 'Date'[Date] <= maxDate)
         
        Correct? That still gives me daily returns as before.