Accumulate Measure Multiplying

marcellope 

I have had a look at your file and existing measures, and uploaded an edited copy with suggested measures here.

Performance seemed acceptable.

Could you could confirm measures are producing correct results (I may well have missed something in the logic!) and whether performance is acceptable for you.

I would recommend measures rather than calculated columns to handle both the Cota and Cumulative Cota calculations. With measures, the "return" calculation can adjust based on all filters, which would be impossible with calculated columns.

It appears that the basic calculation you are wanting to produce is "time-weighted return", in this case expressed as a ratio, i.e. 1 + rate of return.

The updates I made were:

1. Ensure all date filters applied come from CalendarioDAX table.
2. Define measures as follows:
   

   Balance Final = 
   VAR MaxDateFilter =
       MAX ( CalendarioDAX[Date] )
   RETURN
       CALCULATE (
           SUM ( BaseFinal[Saldo Final] ),
           CALCULATETABLE (
               LASTDATE ( SUMMARIZE ( BaseFinal, CalendarioDAX[Date] ) ),
               CalendarioDAX[Date] <= MaxDateFilter
           )
       )
   
   Balance Initial = 
       CALCULATE ( 
           [Balance Final],
           PREVIOUSDAY( CalendarioDAX[Date] )
       )
   
   Movement = 
   SUM ( BaseFinal[Valor Movimentado] )
   
   Cota (Daily) = 
   PRODUCTX ( 
       VALUES ( CalendarioDAX[Date] ),
       DIVIDE ( [Balance Final] - [Movement], [Balance Initial], 1 )
   )
   
   Cota Cumulative (Daily) = 
   VAR MinDate =
       MIN ( CalendarioDAX[Date] )
   VAR DataGlobalMaxDate =
       CALCULATE ( MAX ( BaseFinal[Data] ), ALL ( CalendarioDAX ) )
   RETURN
       IF (
           MinDate <= DataGlobalMaxDate,
           VAR MinDateAllselected =
               CALCULATE ( MIN ( CalendarioDAX[Date] ), ALLSELECTED ( CalendarioDAX ) )
           VAR MaxDate =
               MAX ( CalendarioDAX[Date] )
           RETURN
               CALCULATE (
                   [Cota (Daily)],
                   DATESBETWEEN ( CalendarioDAX[Date], MinDateAllselected, MaxDate )
               )
       )

3. Brief explanation of measures:
   
   • Balance Final returns the most recent balance as at the max filtered date. It does this by finding the last date on which data exists in BaseFinal
   • Balance Initial calculates Balance Final but at the date just before the first filtered date
   • Movement is just the sum of the Valor Movimentado column.
   • Cota (Daily) applies the same logic as your existing Cota measure, but calculated for each date and multiplied. This measure will work in any filtered date range.
   • Cota Cumulative (Daily) calculates Cota (Daily) over all selected dates cumulatively, with a check to blank out results for dates greater than the max date ignorning date filters.
4. These measures seem to perform well enough. The table in screenshot below takes about 4.3 seconds to refresh. One thing to note with these measures is that they produce results on all dates, not just the dates existing in BaseFinal. You could modify these to blank out measures for dates not in BaseFinal if you wanted.
5. Side note: I did attempt alternative versions of the Cota measures: Cota (Detect Movement Dates) and Cota Cumulative (Detect Movement Dates). These measures attempt to reduce the number of calculations using the fact that time-weighted returns can be calculated by splitting dates into blocks between casfhlow dates, and multiplying returns calculated over each block. However, it seems that the DAX required to find the cashflow dates (i.e. dates where Valor Movimentado <> 0) and produce associated date ranges is too much of a drag on performance, so these measures perform worse than the ones proposed above. Never mind.

Regards,

Owen