topic Re: Running total not for a sum but a product (multiplication not addition) in DAX Commands and Tips

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

McShock — Thu, 18 Apr 2024 06:47:26 GMT

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

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

OwenAuger — Thu, 18 Apr 2024 11:33:40 GMT

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

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

McShock — Thu, 18 Apr 2024 12:10:23 GMT

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

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

McShock — Thu, 18 Apr 2024 16:46:24 GMT

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?

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

OwenAuger — Thu, 18 Apr 2024 22:28:22 GMT

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

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

McShock — Sat, 20 Apr 2024 08:49:09 GMT

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

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

OwenAuger — Sun, 21 Apr 2024 12:12:05 GMT

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 🙂