Calculating Cumulative Returns from Daily Returns tables

[Odassier]

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

[johnt75]

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)

[Odassier]

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

[johnt75]

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

[Odassier]

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.

[johnt75]

That looks correct to me. You are using a column from the Date table on your visual, not a column from the MH-ALL table?

Are there any other filters being applied, or any other relationships which could be impacting on the calculation?

[Odassier]

So I have a date table which is associated wtih my Data table (MH-ALL) whcih also has a Date column. But I think the issue is that returns are not additive like Sales numbers etc. but are compounded through time which is why in other BI softwares, like Qlik or Tableu, the expression first converts daily returns into Log returns, then cummulates them (running total in BI speak), then converts the result into an exponent, as per my QLIK/Tableau expression below.

Olivier

[v-yiruan-msft]

Hi @Odassier ,

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

[Odassier]

Hi all, 

I'm attaching the link to a sample pbix file here to help with this issue.

As you can see from the chart on the dashboard, there is a slight difference from the running total sum of daily returns and the cumulative return computed in excel. This comes from the fact that daily returns need to be counpounded over time and are not additive. 

If anyopne has a dashboard with a calculated measure for cumulative (investment) return, that could be great. 

File can be found here: https://www.dropbox.com/s/w6hdayywzl48wcf/SAP500_CumReturn.pbix?dl=0

Much obliged,

Olivier

[Odassier]

Hi @v-yiruan-msft,

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

[johnt75]

Ah, sorry, can't help with that

[Odassier]

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.