topic Re: Cumulative Time Weighted Rate of Return in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4136180#M164304 <P>Here's a sample data where the bold columns are from database</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><STRONG><FONT color="#000000">Cuenta</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Date</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Value Open</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Cashflow</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Value Close</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Daily Return</FONT></STRONG></TD><TD>TWR</TD></TR><TR><TD>1</TD><TD>01-01-2024</TD><TD>2523751</TD><TD>$0</TD><TD>$2.523.807,34</TD><TD>0,00%</TD><TD>0%</TD></TR><TR><TD>1</TD><TD>02-01-2024</TD><TD>2523807</TD><TD>$0</TD><TD>$2.525.654,86</TD><TD>0,07%</TD><TD>0,070%</TD></TR><TR><TD>1</TD><TD>03-01-2024</TD><TD>2525655</TD><TD>$0</TD><TD>$2.532.308,65</TD><TD>0,26%</TD><TD>0,330%</TD></TR><TR><TD>1</TD><TD>04-01-2024</TD><TD>2532309</TD><TD>$0</TD><TD>$2.530.964,91</TD><TD>-0,05%</TD><TD>0,280%</TD></TR><TR><TD>1</TD><TD>05-01-2024</TD><TD>2530965</TD><TD>$0</TD><TD>$2.530.438,70</TD><TD>-0,02%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>06-01-2024</TD><TD>2530439</TD><TD>$0</TD><TD>$2.530.479,70</TD><TD>0,00%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>07-01-2024</TD><TD>2530480</TD><TD>$0</TD><TD>$2.530.518,70</TD><TD>0,00%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>08-01-2024</TD><TD>2530519</TD><TD>$0</TD><TD>$2.517.846,80</TD><TD>-0,50%</TD><TD>-0,241%</TD></TR><TR><TD>1</TD><TD>09-01-2024</TD><TD>2517847</TD><TD>$0</TD><TD>$2.490.197,86</TD><TD>-1,10%</TD><TD>-1,339%</TD></TR><TR><TD>1</TD><TD>10-01-2024</TD><TD>2490198</TD><TD>$0</TD><TD>$2.492.815,65</TD><TD>0,11%</TD><TD>-1,230%</TD></TR><TR><TD>1</TD><TD>11-01-2024</TD><TD>2492816</TD><TD>$0</TD><TD>$2.528.635,07</TD><TD>1,44%</TD><TD>0,192%</TD></TR><TR><TD>1</TD><TD>12-01-2024</TD><TD>2528635</TD><TD>$0</TD><TD>$2.516.607,50</TD><TD>-0,48%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>13-01-2024</TD><TD>2516608</TD><TD>$0</TD><TD>$2.516.603,50</TD><TD>0,00%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>14-01-2024</TD><TD>2516604</TD><TD>$0</TD><TD>$2.516.591,41</TD><TD>0,00%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>15-01-2024</TD><TD>2516591</TD><TD>$0</TD><TD>$2.510.388,94</TD><TD>-0,25%</TD><TD>-0,538%</TD></TR><TR><TD>1</TD><TD>16-01-2024</TD><TD>2510389</TD><TD>$0</TD><TD>$2.559.804,49</TD><TD>1,97%</TD><TD>1,421%</TD></TR><TR><TD>1</TD><TD>17-01-2024</TD><TD>2559804</TD><TD>$0</TD><TD>$2.540.929,49</TD><TD>-0,74%</TD><TD>0,671%</TD></TR><TR><TD>1</TD><TD>18-01-2024</TD><TD>2540929</TD><TD>$0</TD><TD>$2.521.969,35</TD><TD>-0,75%</TD><TD>-0,084%</TD></TR><TR><TD>1</TD><TD>19-01-2024</TD><TD>2521969</TD><TD>$0</TD><TD>$2.510.432,34</TD><TD>-0,46%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>20-01-2024</TD><TD>2510432</TD><TD>$0</TD><TD>$2.510.419,23</TD><TD>0,00%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>21-01-2024</TD><TD>2510419</TD><TD>$0</TD><TD>$2.510.416,23</TD><TD>0,00%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>22-01-2024</TD><TD>2510416</TD><TD>$0</TD><TD>$2.522.545,94</TD><TD>0,48%</TD><TD>-0,066%</TD></TR><TR><TD>1</TD><TD>23-01-2024</TD><TD>2522546</TD><TD>$0</TD><TD>$2.494.020,60</TD><TD>-1,13%</TD><TD>-1,196%</TD></TR><TR><TD>1</TD><TD>24-01-2024</TD><TD>2494021</TD><TD>$0</TD><TD>$2.565.106,74</TD><TD>2,85%</TD><TD>1,620%</TD></TR><TR><TD>1</TD><TD>25-01-2024</TD><TD>2565107</TD><TD>$0</TD><TD>$2.564.658,42</TD><TD>-0,02%</TD><TD>1,600%</TD></TR><TR><TD>1</TD><TD>26-01-2024</TD><TD>2564658</TD><TD>$0</TD><TD>$2.584.099,95</TD><TD>0,76%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>27-01-2024</TD><TD>2584100</TD><TD>$0</TD><TD>$2.584.095,95</TD><TD>0,00%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>28-01-2024</TD><TD>2584096</TD><TD>$0</TD><TD>$2.584.080,78</TD><TD>0,00%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>29-01-2024</TD><TD>2584081</TD><TD>$0</TD><TD>$2.605.192,92</TD><TD>0,82%</TD><TD>3,212%</TD></TR><TR><TD>1</TD><TD>30-01-2024</TD><TD>2605193</TD><TD>$0</TD><TD>$2.606.311,09</TD><TD>0,04%</TD><TD>3,253%</TD></TR><TR><TD>1</TD><TD>31-01-2024</TD><TD>2606311</TD><TD>$0</TD><TD>$2.604.802,06</TD><TD>-0,06%</TD><TD>3,191%</TD></TR></TBODY></TABLE><P><BR />TWR is&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">Time-Weighted Return (non annualized) = PRODUCTX ( 'Calendario', DIVIDE ( [Value Close Total] - [Cashflow Total], [Value Open Total] ) ) - 1</LI-CODE><LI-CODE lang="markup">Value Close Total = SUM(Patrimonios[Value Close])</LI-CODE><LI-CODE lang="markup">Value Open Total = SUM(Patrimonios[Value Open])</LI-CODE><LI-CODE lang="markup">Cashflow Total = SUM(Patrimonios[Giros y Abonos Netos])</LI-CODE><P>&nbsp;</P><P>all my data has the structure to use this<BR /><A href="https://owenaugerbi.com/time-weighted-return-and-money-weighted-return/" target="_blank" rel="noopener">Time-Weighted Return and Money-Weighted Return – Owen Auger's BI Blog (owenaugerbi.com)</A><BR />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<BR />this will lead in this chart</P><P><img /></P><P>&nbsp;</P><P><BR /><BR /><BR /><BR /><BR /></P> Thu, 05 Sep 2024 20:42:44 GMT Anonymous 2024-09-05T20:42:44Z Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4135870#M164283 <P>Hi,</P><P>&nbsp;I'm trying to build a chart like this</P><DIV class="">&nbsp;</DIV><P><img /><BR />And i'm using the calculations made by&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="6799" data-lia-user-login="OwenAuger" class="lia-mention lia-mention-user">OwenAuger</a>&nbsp; at&nbsp;<A href="https://owenaugerbi.com/time-weighted-return-and-money-weighted-return/" target="_blank" rel="noopener">Time-Weighted Return and Money-Weighted Return – Owen Auger's BI Blog (owenaugerbi.com)</A><BR /><BR />This is what i need to calculate for each day until the last<BR /><BR /></P><P><img /></P><P>&nbsp;</P><P><BR />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.<BR /><BR /><BR /><BR /></P> Thu, 05 Sep 2024 16:17:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4135870#M164283 Anonymous 2024-09-05T16:17:05Z Re: Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4135927#M164285 <P>I do this sort of calculation all the time. Can you share specific data and expected return? I didn't "<SPAN>find the dashboard link in the message I shared earlier</SPAN>".</P> <P>&nbsp;</P> <P><A href="https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523" target="_blank">How to Get Your Question Answered Quickly - Microsoft Fabric Community</A></P> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 05 Sep 2024 16:47:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4135927#M164285 AlexisOlson 2024-09-05T16:47:14Z Re: Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4136180#M164304 <P>Here's a sample data where the bold columns are from database</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><STRONG><FONT color="#000000">Cuenta</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Date</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Value Open</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Cashflow</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Value Close</FONT></STRONG></TD><TD><STRONG><FONT color="#000000">Daily Return</FONT></STRONG></TD><TD>TWR</TD></TR><TR><TD>1</TD><TD>01-01-2024</TD><TD>2523751</TD><TD>$0</TD><TD>$2.523.807,34</TD><TD>0,00%</TD><TD>0%</TD></TR><TR><TD>1</TD><TD>02-01-2024</TD><TD>2523807</TD><TD>$0</TD><TD>$2.525.654,86</TD><TD>0,07%</TD><TD>0,070%</TD></TR><TR><TD>1</TD><TD>03-01-2024</TD><TD>2525655</TD><TD>$0</TD><TD>$2.532.308,65</TD><TD>0,26%</TD><TD>0,330%</TD></TR><TR><TD>1</TD><TD>04-01-2024</TD><TD>2532309</TD><TD>$0</TD><TD>$2.530.964,91</TD><TD>-0,05%</TD><TD>0,280%</TD></TR><TR><TD>1</TD><TD>05-01-2024</TD><TD>2530965</TD><TD>$0</TD><TD>$2.530.438,70</TD><TD>-0,02%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>06-01-2024</TD><TD>2530439</TD><TD>$0</TD><TD>$2.530.479,70</TD><TD>0,00%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>07-01-2024</TD><TD>2530480</TD><TD>$0</TD><TD>$2.530.518,70</TD><TD>0,00%</TD><TD>0,260%</TD></TR><TR><TD>1</TD><TD>08-01-2024</TD><TD>2530519</TD><TD>$0</TD><TD>$2.517.846,80</TD><TD>-0,50%</TD><TD>-0,241%</TD></TR><TR><TD>1</TD><TD>09-01-2024</TD><TD>2517847</TD><TD>$0</TD><TD>$2.490.197,86</TD><TD>-1,10%</TD><TD>-1,339%</TD></TR><TR><TD>1</TD><TD>10-01-2024</TD><TD>2490198</TD><TD>$0</TD><TD>$2.492.815,65</TD><TD>0,11%</TD><TD>-1,230%</TD></TR><TR><TD>1</TD><TD>11-01-2024</TD><TD>2492816</TD><TD>$0</TD><TD>$2.528.635,07</TD><TD>1,44%</TD><TD>0,192%</TD></TR><TR><TD>1</TD><TD>12-01-2024</TD><TD>2528635</TD><TD>$0</TD><TD>$2.516.607,50</TD><TD>-0,48%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>13-01-2024</TD><TD>2516608</TD><TD>$0</TD><TD>$2.516.603,50</TD><TD>0,00%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>14-01-2024</TD><TD>2516604</TD><TD>$0</TD><TD>$2.516.591,41</TD><TD>0,00%</TD><TD>-0,289%</TD></TR><TR><TD>1</TD><TD>15-01-2024</TD><TD>2516591</TD><TD>$0</TD><TD>$2.510.388,94</TD><TD>-0,25%</TD><TD>-0,538%</TD></TR><TR><TD>1</TD><TD>16-01-2024</TD><TD>2510389</TD><TD>$0</TD><TD>$2.559.804,49</TD><TD>1,97%</TD><TD>1,421%</TD></TR><TR><TD>1</TD><TD>17-01-2024</TD><TD>2559804</TD><TD>$0</TD><TD>$2.540.929,49</TD><TD>-0,74%</TD><TD>0,671%</TD></TR><TR><TD>1</TD><TD>18-01-2024</TD><TD>2540929</TD><TD>$0</TD><TD>$2.521.969,35</TD><TD>-0,75%</TD><TD>-0,084%</TD></TR><TR><TD>1</TD><TD>19-01-2024</TD><TD>2521969</TD><TD>$0</TD><TD>$2.510.432,34</TD><TD>-0,46%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>20-01-2024</TD><TD>2510432</TD><TD>$0</TD><TD>$2.510.419,23</TD><TD>0,00%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>21-01-2024</TD><TD>2510419</TD><TD>$0</TD><TD>$2.510.416,23</TD><TD>0,00%</TD><TD>-0,544%</TD></TR><TR><TD>1</TD><TD>22-01-2024</TD><TD>2510416</TD><TD>$0</TD><TD>$2.522.545,94</TD><TD>0,48%</TD><TD>-0,066%</TD></TR><TR><TD>1</TD><TD>23-01-2024</TD><TD>2522546</TD><TD>$0</TD><TD>$2.494.020,60</TD><TD>-1,13%</TD><TD>-1,196%</TD></TR><TR><TD>1</TD><TD>24-01-2024</TD><TD>2494021</TD><TD>$0</TD><TD>$2.565.106,74</TD><TD>2,85%</TD><TD>1,620%</TD></TR><TR><TD>1</TD><TD>25-01-2024</TD><TD>2565107</TD><TD>$0</TD><TD>$2.564.658,42</TD><TD>-0,02%</TD><TD>1,600%</TD></TR><TR><TD>1</TD><TD>26-01-2024</TD><TD>2564658</TD><TD>$0</TD><TD>$2.584.099,95</TD><TD>0,76%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>27-01-2024</TD><TD>2584100</TD><TD>$0</TD><TD>$2.584.095,95</TD><TD>0,00%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>28-01-2024</TD><TD>2584096</TD><TD>$0</TD><TD>$2.584.080,78</TD><TD>0,00%</TD><TD>2,372%</TD></TR><TR><TD>1</TD><TD>29-01-2024</TD><TD>2584081</TD><TD>$0</TD><TD>$2.605.192,92</TD><TD>0,82%</TD><TD>3,212%</TD></TR><TR><TD>1</TD><TD>30-01-2024</TD><TD>2605193</TD><TD>$0</TD><TD>$2.606.311,09</TD><TD>0,04%</TD><TD>3,253%</TD></TR><TR><TD>1</TD><TD>31-01-2024</TD><TD>2606311</TD><TD>$0</TD><TD>$2.604.802,06</TD><TD>-0,06%</TD><TD>3,191%</TD></TR></TBODY></TABLE><P><BR />TWR is&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">Time-Weighted Return (non annualized) = PRODUCTX ( 'Calendario', DIVIDE ( [Value Close Total] - [Cashflow Total], [Value Open Total] ) ) - 1</LI-CODE><LI-CODE lang="markup">Value Close Total = SUM(Patrimonios[Value Close])</LI-CODE><LI-CODE lang="markup">Value Open Total = SUM(Patrimonios[Value Open])</LI-CODE><LI-CODE lang="markup">Cashflow Total = SUM(Patrimonios[Giros y Abonos Netos])</LI-CODE><P>&nbsp;</P><P>all my data has the structure to use this<BR /><A href="https://owenaugerbi.com/time-weighted-return-and-money-weighted-return/" target="_blank" rel="noopener">Time-Weighted Return and Money-Weighted Return – Owen Auger's BI Blog (owenaugerbi.com)</A><BR />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<BR />this will lead in this chart</P><P><img /></P><P>&nbsp;</P><P><BR /><BR /><BR /><BR /><BR /></P> Thu, 05 Sep 2024 20:42:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4136180#M164304 Anonymous 2024-09-05T20:42:44Z Re: Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4136271#M164309 <P>You've already got a daily return column. Just link those together with a classic cumulative pattern like this:</P> <P>&nbsp;</P> <LI-CODE lang="php">VAR _CurrDate = MAX ( 'Calendario'[Date] ) VAR _Dates_ = FILTER ( ALLSELECTED ( 'Calendario' ), 'Calendario'[Date] &lt;= _CurrDate ) VAR _Product = PRODUCTX ( _Dates_, 1 + CALCULATE ( SELECTEDVALUE ( Patrimonios[Daily Return] ) ) ) VAR _Result = IF ( ISBLANK ( _Product ), BLANK (), _Product - 1 ) RETURN _Result</LI-CODE> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> Thu, 05 Sep 2024 22:29:40 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4136271#M164309 AlexisOlson 2024-09-05T22:29:40Z Re: Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4137858#M164363 <P>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.<BR /><BR /></P><P><img /></P><P>&nbsp;</P> Fri, 06 Sep 2024 14:36:26 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4137858#M164363 Anonymous 2024-09-06T14:36:26Z Re: Cumulative Time Weighted Rate of Return https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4138352#M164391 <P>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.</P> Fri, 06 Sep 2024 20:31:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-Time-Weighted-Rate-of-Return/m-p/4138352#M164391 AlexisOlson 2024-09-06T20:31:36Z