Cumulative Time Weighted Rate of Return

[alvaro_miranda]

Hi,

 I'm trying to build a chart like this

And i'm using the calculations made by @OwenAuger  at Time-Weighted Return and Money-Weighted Return – Owen Auger's BI Blog (owenaugerbi.com)

This is what i need to calculate for each day until the last

The issue is that the correct TWR (Time-Weighted Return) value is only given at the total level of the selected period, but I'm unable to calculate the cumulative value. Could someone help me with these calculations? You can find the dashboard link in the message I shared earlier.

[alvaro_miranda]

Here's a sample data where the bold columns are from database

Cuenta	Date	Value Open	Cashflow	Value Close	Daily Return	TWR
1	01-01-2024	2523751	$0	$2.523.807,34	0,00%	0%
1	02-01-2024	2523807	$0	$2.525.654,86	0,07%	0,070%
1	03-01-2024	2525655	$0	$2.532.308,65	0,26%	0,330%
1	04-01-2024	2532309	$0	$2.530.964,91	-0,05%	0,280%
1	05-01-2024	2530965	$0	$2.530.438,70	-0,02%	0,260%
1	06-01-2024	2530439	$0	$2.530.479,70	0,00%	0,260%
1	07-01-2024	2530480	$0	$2.530.518,70	0,00%	0,260%
1	08-01-2024	2530519	$0	$2.517.846,80	-0,50%	-0,241%
1	09-01-2024	2517847	$0	$2.490.197,86	-1,10%	-1,339%
1	10-01-2024	2490198	$0	$2.492.815,65	0,11%	-1,230%
1	11-01-2024	2492816	$0	$2.528.635,07	1,44%	0,192%
1	12-01-2024	2528635	$0	$2.516.607,50	-0,48%	-0,289%
1	13-01-2024	2516608	$0	$2.516.603,50	0,00%	-0,289%
1	14-01-2024	2516604	$0	$2.516.591,41	0,00%	-0,289%
1	15-01-2024	2516591	$0	$2.510.388,94	-0,25%	-0,538%
1	16-01-2024	2510389	$0	$2.559.804,49	1,97%	1,421%
1	17-01-2024	2559804	$0	$2.540.929,49	-0,74%	0,671%
1	18-01-2024	2540929	$0	$2.521.969,35	-0,75%	-0,084%
1	19-01-2024	2521969	$0	$2.510.432,34	-0,46%	-0,544%
1	20-01-2024	2510432	$0	$2.510.419,23	0,00%	-0,544%
1	21-01-2024	2510419	$0	$2.510.416,23	0,00%	-0,544%
1	22-01-2024	2510416	$0	$2.522.545,94	0,48%	-0,066%
1	23-01-2024	2522546	$0	$2.494.020,60	-1,13%	-1,196%
1	24-01-2024	2494021	$0	$2.565.106,74	2,85%	1,620%
1	25-01-2024	2565107	$0	$2.564.658,42	-0,02%	1,600%
1	26-01-2024	2564658	$0	$2.584.099,95	0,76%	2,372%
1	27-01-2024	2584100	$0	$2.584.095,95	0,00%	2,372%
1	28-01-2024	2584096	$0	$2.584.080,78	0,00%	2,372%
1	29-01-2024	2584081	$0	$2.605.192,92	0,82%	3,212%
1	30-01-2024	2605193	$0	$2.606.311,09	0,04%	3,253%
1	31-01-2024	2606311	$0	$2.604.802,06	-0,06%	3,191%

TWR is 

Time-Weighted Return (non annualized) = 
PRODUCTX (
    'Calendario',
    DIVIDE ( [Value Close Total] - [Cashflow Total], [Value Open Total] )
)
    - 1

Value Close Total = SUM(Patrimonios[Value Close])

Value Open Total = SUM(Patrimonios[Value Open])

Cashflow Total = SUM(Patrimonios[Giros y Abonos Netos])

all my data has the structure to use this
Time-Weighted Return and Money-Weighted Return – Owen Auger's BI Blog (owenaugerbi.com)
As I said the hart part is forcing the TWR to be zero when it's the first date in the context and then every other row is calculated as TWR(t) = (1+TWR(t-1))*(1+Return(t)) - 1
this will lead in this chart

[AlexisOlson]

You've already got a daily return column. Just link those together with a classic cumulative pattern like this:

VAR _CurrDate = MAX ( 'Calendario'[Date] )
VAR _Dates_ =
    FILTER ( ALLSELECTED ( 'Calendario' ), 'Calendario'[Date] <= _CurrDate )
VAR _Product =
    PRODUCTX (
        _Dates_,
        1 + CALCULATE ( SELECTEDVALUE ( Patrimonios[Daily Return] ) )
    )
VAR _Result =
    IF ( ISBLANK ( _Product ), BLANK (), _Product - 1 )
RETURN
    _Result

[alvaro_miranda]

Wow, it almost works as I expected. I want it to start at 0, and then the second row should recognize that the first value is 0. I implemented your measure, and this is what I got.

[AlexisOlson]

I don't understand why you'd want to ignore the return for the first day, but you could filter the _Dates_ table to remove the row with the minimal date.

[AlexisOlson]

I do this sort of calculation all the time. Can you share specific data and expected return? I didn't "find the dashboard link in the message I shared earlier".

How to Get Your Question Answered Quickly - Microsoft Fabric Community