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...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

Please follow the below steps to get the culmulative values, you can get the details in the attachment.

1. Delete the relationship between the table 'MH-ALL' and 'Date'

2. Apply the date field of Date dimension table on X axis of your visual

3. Update the formula of measure [Cumm Total] as below

Cumm Total = 
CALCULATE (
    SUM ( 'MH-ALL'[Dividend Yield] ),
    FILTER (
        ALLSELECTED ( 'MH-ALL' ),
        'MH-ALL'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous,

 

Thanks for this, it is very helpful. Unfortunately, this only works witjh data that is additive, like sales orders or goods sold etc. and which accrue over time. Financials returns compound over time and are not additive. So if you compute cumulative returns in excel you get slightly different values through time (see chart below with your additive formula in grey and the excel computed cumulative returns in orange. The longer the period, the more material the difference will be between the two methods. 

Returns are not additive, but log returns are, which is why in the comments above I mention that in Qlik or Tableu the expressin we use first converts the daily returns data into log returns, then adds them through time, then takes the exponent of the final value to convert them back into returns. (e.g, exp(RangeSum(Above(log(1+([Dividend Yield]/100)), 0, RowNo()))) - 1.

 

So, what I need is a DAX expression that will do the following steps: 

  1. Compute y_t=log(1+r_t) where r_t is the return in period t.
  2. Compute running sum of y_t and call it z_t
  3. cumulative value at time t is then exp(z_t)

Does that make sense?

 

Thanks

 

Olivier