Running total not for a sum but a product (multiplication not addition)

[McShock]

Hi, I wonder if there is any way to calculate with DAX kind of a running total but instead of adding the values by multiplying the values. I give a concrete example: 

I have a table with 4 yearly returns betwen 2020 and 2023 (+10%, -10%, -5%, +30%) and I want to know what my overall return is since the beginning (31/12/2020). Because of the compounding nature of returns I can not add the returns but need to multiplying them. Adding them would give +25% after 4 years but compounding them yields +22.3%.

In Excel I would do this: 

         

But how can this be done in PBI? 

Thank you in advance for your brain power.

Markus

[McShock]

Hi Owen, this is extremely helpful. Thank you very much!

[OwenAuger · Accepted solution]

Hi @McShock 

The PRODUCT or PRODUCTX functions are what I would suggest here.

As an initial suggestion, see the attached PBIX.

Return table:

Related to Date table:

And these are some initial measures you could create.

Return Index is the base measure that returns the product of (1+return) for each period.

Return Index = 
PRODUCTX ( 'Return', 1 + 'Return'[Yearly Return] )

Return = 
VAR ReturnIndex = [Return Index]
RETURN
    IF (
        NOT ISBLANK ( ReturnIndex ),
        ReturnIndex - 1
    )

Cumulative Return Index = 
CALCULATE (
    [Return Index],
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

Cumulative Return = 
CALCULATE (
    [Return],
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

Sample visual:

The above is intended to illustrate the basic logic. Depending on your overall model, some adjustments to the measures might be needed.

Hopefully this is some help!

Regards

[McShock]

Hi, I can not replicate this. Not even the first measure

I think the problem is, that my returns are the result of a caluculation within a measure whereas yours are in a column of a table. Can you guide me how to handle this?

[OwenAuger]

Hi again @McShock 

Sure! 🙂

Assuming you already have a measure which produces return values by year say, you would then use PRODUCTX to multiply by year.

Let's assume Yearly Return Base Measure is the "base measure" for return by year (replace with your existing measure):

Yearly Return Base Measure = 
// Replace with appropriate definition
PRODUCTX ( 'Return', 1 + 'Return'[Yearly Return] ) - 1

Then you would write Return Index as follows:

Return Index = 
PRODUCTX ( VALUES ( 'Date'[Year] ), 1 + [Yearly Return Base Measure] )

The other measures would remain the same as before.

Updated PBIX attached.

Regards

[McShock]

Hi, sorry to disturb you again. I am getting closer but some dax is not working properly and I do not know what I am doing wrong.

What I have so far is:

Monthly returns: 

Return Index (interestingly it does not show the total)

Return

But the Monthly Return Base Measure yields 0 for all months:

Any idea why this happens?

Thank you in advance

[OwenAuger]

Hi again @McShock 

The measure Yearly Return Base Measure in my earlier reply was actually intended to be the equivalent of your Performance %.

Based on your screenshots, and assuming Performance % is evaluated monthly, I would define the measures in this sequence (I slightly adjusted compared to earlier version):

Performance %

As per your definition (I have used a dummy definition).

Return Index

Return Index = 
PRODUCTX (
    VALUES ( 'Date'[Month Start Date] ),
    VAR PerformancePct = [Performance %]
    RETURN
        IF (
            NOT ISBLANK ( PerformancePct ),
            1 + PerformancePct
        )
)

Return

Return = 
VAR ReturnIndex = [Return Index]
RETURN
    IF (
        NOT ISBLANK ( ReturnIndex ),
        ReturnIndex - 1
    )

Cumulative Return Index

Ensures values don't go beyond last month with data

Cumulative Return Index = 
VAR MaxMonthGlobal =
    EOMONTH ( CALCULATE ( MAX ( 'Return'[Date] ), REMOVEFILTERS () ), 0 )
-- Min/Max dates in current context
VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )
RETURN
    IF (
        MinDate <= MaxMonthGlobal,
        CALCULATE (
            [Return Index],
            'Date'[Date] <= MaxDate
        )
    )

Cumulative Return

Cumulative Return = 
VAR CumulativeReturnIndex = [Cumulative Return Index]
RETURN
    IF (
        NOT ISBLANK ( CumulativeReturnIndex ),
        CumulativeReturnIndex - 1
    )

PBIX attached 🙂