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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
McShock
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: 

 

Calculating Returns.png         Calculating Returns2.png

 

But how can this be done in PBI? 

 

Thank you in advance for your brain power.

 

Markus

1 ACCEPTED SOLUTION
OwenAuger
Super User
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:

OwenAuger_0-1713439620115.png

 

Related to Date table:

OwenAuger_1-1713439664875.png

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:

OwenAuger_2-1713439952477.png

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

View solution in original post

6 REPLIES 6
McShock
Frequent Visitor

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

OwenAuger
Super User
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:

OwenAuger_0-1713439620115.png

 

Related to Date table:

OwenAuger_1-1713439664875.png

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:

OwenAuger_2-1713439952477.png

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

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

McShock_0-1713458357841.png

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?

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

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: 

McShock_2-1713602556780.png

Return Index (interestingly it does not show the total)

 

 

McShock_1-1713602480384.png

Return

McShock_3-1713602682702.png

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

McShock_4-1713602780511.png

 

Any idea why this happens?

 

Thank you in advance

 

 

 

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
    )

OwenAuger_0-1713701440583.png

 

PBIX attached 🙂


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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors