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
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
Solved! Go to 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
Hi Owen, this is extremely helpful. Thank you very much!
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
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?
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
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
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 🙂