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

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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

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

      Ah, sorry, can't help with that

    • 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