cancel
Showing results 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

## Cumulative Time Weighted Rate of Return

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.

5 REPLIES 5
Frequent Visitor

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

Super User

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``````

Frequent Visitor

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.

Super User

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.

Super User

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".

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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors