Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
6 REPLIES 6
Frequent Visitor

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

Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Frequent Visitor

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?

Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Frequent Visitor

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

Super User

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 🙂

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors