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

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

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