Accumulate Measure Multiplying

[marcellope]

Hi All!

I'm looking for this solution a long time, but i did not find it yet. Let's go:

I have a measure calculating the return of a portfolio day by day. It looks like this:

Now, i have to multiply and accumulate the values, day by day. It will look like this:

A simple DAX formula would do the job, like Product(Measure). However, "PRODUCT" formula only accept columns, not measures.

I found another DAX formula: 

CALCULATE(PRODUCTX(CALENDAR;[Cota]);FILTER(ALLSELECTED(CALENDAR[Date]);CALENDAR[Date]<=MAX(CALENDAR[Date])))

This formula works, however, it is so heavy and slow that Power Bi can't complete the request (no memory enough), and calculating more than 5 minutes in a row.

Anyone can help?

Thanks!

[Anonymous]

Dear  @OwenAuger 

Thank you so much for the extremely helpful information and for sharing the pbi report above.

I am trying to create a report similar to the one you shared above but based on my table containing the following columns: date, stock symbol, open price, close price, cashflow, stock balance change (# of stocks bought/sold). Note that the final daily balance (same as "BaseFinal[Saldo Final]" in your report) is not in the table and thus I believe should be calculated either as a column or a measure in order to create "Balance Final" measure you mentioned above (also copied below for reference). Thanks to the report you shared, I figured out a way to calculate the cumulative number of stocks owned on a each day for each stock symbol ("Stocks Cumulative (Daily)" measure below). However, I am unable to calculate a final daily balance (basically "close price"*"Stocks Cumulative (Daily)") for each symbol that would aggregate correctly. Considering this, could you please recommend the best way to calculate final balance ($$) for each symbol with a daily granularity? (I believe the simplest way would be to transform "Stocks Cumulative (Daily)" measure into a calculated column and then adding another calculated column to multiple "Stocks Cumulative (Daily)" by "close price", but have no idea how to do it or if it is even feasible).

My measure to calculate the cumulative number of stocks owned on a each day for each stock symbol (based on your "Cota Cumulative (Daily)" measure):

Stocks Cumulative (Daily) = 

VAR MinDate =
    MIN ( CalendarioDAX[Date] )
VAR DataGlobalMaxDate =
    CALCULATE ( MAX ( DailyStockPrices[Date] ), ALL ( CalendarioDAX ) )
RETURN
    IF (
        MinDate <= DataGlobalMaxDate,
        VAR MinDateAllselected =
            CALCULATE ( MIN ( CalendarioDAX[Date] ), ALL ( CalendarioDAX ) )
        VAR MaxDate =
            MAX ( CalendarioDAX[Date] )
        RETURN
            CALCULATE (
                SUM(DailyStockPrices[StockBalanceChange), 
                DATESBETWEEN ( CalendarioDAX[Date], MinDateAllselected, MaxDate )
            ) 
    )

Your original measure (just for reference) that I am trying to replicate, but cannot (due to lack of BaseFinal[Saldo Final] column in my table):

Balance Final = 
VAR MaxDateFilter =
    MAX ( CalendarioDAX[Date] )
RETURN
    CALCULATE (
        SUM ( BaseFinal[Saldo Final] ),
        CALCULATETABLE (
            LASTDATE ( SUMMARIZE ( BaseFinal, CalendarioDAX[Date] ) ),
            CalendarioDAX[Date] <= MaxDateFilter
        )
    )

Thank you so much in advance!

[Anonymous]

First of all, some questions.

Why do you need a measure? Why is a calculated column not an option? Do you want to calculate such a product only over the visible days - does it make sense? Or rather, you'd like to calculate for each portfolio and each day the cumulative return? If this is the case (and I think it would make more sense), you should do this in Power Query, not in DAX.

Once you have a helper table with all the portfolios and dates and the cumulative return, you can create a simple measure that will return the pre-calculated amounts...

Would that not be the fastest way?

Best
Darek

[marcellope]

@Anonymous 

I need a measure because a calculated column does not work in this case. Let me explain:

I have 2 different investments in 2 differentes banks at the same day:

Bank 1 = 10k

Bank 2 = 20k

Total: 30k

If I want to apply a filter only in bank 1 = 10k, the calculated column already has the value 30k. I tried to do a variable calculated column, but I don’t know how to do that (recalculate the column everytime a filter is applied).

Yes, is only in visible days. The problem is I’m not familiar with Power Query.

The problem goes on...

[OwenAuger · Accepted solution]

@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

[marcellope]

@OwenAuger 

Man, it worked like a miracle. That was exaclty what I was looking for.

Thank you so much!

[Anonymous]

You CANNOT recalculate a calculated column. Once it's been calculated, it's set in stone.

I'll have a look at the file.

Best
D

[marcellope]

@OwenAuger 

Sure, here it goes the file.

https://mega.nz/#!WaQylKrY!tGJrKhhN7ai9haKMWD7LDLr6C6agtNx4Gqt0ppzLkiA

Regards,

[OwenAuger]

Hi @marcellope 

Can you share the definition of your [Cota] measure?

Also a PBIX with dummy data or a diagram of the data modelwould be useful.

Depending how [Cota] is defined, we may be able to simplify the calculation of the compounded return that you are trying to produce.

Regards,

Owen