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 use Power BI as an interactive dashboard allowing the user to select custom date ranges and see the portfolio's cumulative returns in a line chart. This means the cumulative returns needs to be recomputed on the fly to show the starting date's return on day one and cumulative going forward.

 

I did see several posts in the forums with the subject "cumulative return" but their usecase was not a match and I could not (as a newbie) figure out how to adapt the DAX experssion for my purpose. 

 

Any help is greatly appreciated.

 

Olivier

 

 

11 Replies

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