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