https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Accumulate-Column/m-p/4022330#M158707 <P>Hello,&nbsp;</P><P>&nbsp;</P><P>I have a list of contracts and am trying to figure out a way to accumulate the sum of active contracts over the year.&nbsp;</P><P>&nbsp;</P><P>My Datatable looks something like this:&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Counter</TD><TD>&nbsp;</TD><TD>StartDate</TD><TD>&nbsp;</TD><TD>Enddate</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>31.12.2028</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>31.12.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2023</TD><TD>&nbsp;</TD><TD>01.02.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2002</TD><TD>&nbsp;</TD><TD>01.03.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2004</TD><TD>&nbsp;</TD><TD>01.06.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.06.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>02.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>03.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>04.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>05.05.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>06.06.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>07.07.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2013</TD><TD>&nbsp;</TD><TD>01.02.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2012</TD><TD>&nbsp;</TD><TD>01.08.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2015</TD><TD>&nbsp;</TD><TD>01.09.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2016</TD><TD>&nbsp;</TD><TD>01.03.2024</TD></TR></TBODY></TABLE><P>Now its no problem to calculate the to number of active contracts for any given month, but I cant figure out a way to accumulate them over the year. My goal would be a table that looks like this for the current example:&nbsp;</P><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>&nbsp;</TD><TD>Counter</TD><TD>&nbsp;</TD><TD>Accumulated</TD></TR><TR><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>10</TD><TD>&nbsp;</TD><TD>10</TD></TR><TR><TD>01.02.2024</TD><TD>&nbsp;</TD><TD>11</TD><TD>&nbsp;</TD><TD>21</TD></TR><TR><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>34</TD></TR><TR><TD>01.04.2024</TD><TD>&nbsp;</TD><TD>12</TD><TD>&nbsp;</TD><TD>46</TD></TR><TR><TD>01.05.2024</TD><TD>&nbsp;</TD><TD>12</TD><TD>&nbsp;</TD><TD>58</TD></TR><TR><TD>01.06.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>72</TD></TR><TR><TD>01.07.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>86</TD></TR><TR><TD>01.08.2024</TD><TD>&nbsp;</TD><TD>15</TD><TD>&nbsp;</TD><TD>101</TD></TR><TR><TD>01.09.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>115</TD></TR><TR><TD>01.10.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>128</TD></TR><TR><TD>01.11.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>141</TD></TR><TR><TD>01.12.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>154</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I could not find an example for this specific Problem on the Forums and all solutions I found are not quite working for this situation.&nbsp;</P><P>&nbsp;</P><P>Any help or ideas would be greatly appreciated.&nbsp;</P> Wed, 03 Jul 2024 08:19:02 GMT TKBI 2024-07-03T08:19:02Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Accumulate-Column/m-p/4022330#M158707 <P>Hello,&nbsp;</P><P>&nbsp;</P><P>I have a list of contracts and am trying to figure out a way to accumulate the sum of active contracts over the year.&nbsp;</P><P>&nbsp;</P><P>My Datatable looks something like this:&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Counter</TD><TD>&nbsp;</TD><TD>StartDate</TD><TD>&nbsp;</TD><TD>Enddate</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>31.12.2028</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>31.12.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2023</TD><TD>&nbsp;</TD><TD>01.02.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2002</TD><TD>&nbsp;</TD><TD>01.03.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2004</TD><TD>&nbsp;</TD><TD>01.06.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.06.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>02.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>03.02.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>04.03.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>05.05.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>06.06.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>07.07.2024</TD><TD>&nbsp;</TD><TD>01.08.2026</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2013</TD><TD>&nbsp;</TD><TD>01.02.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2012</TD><TD>&nbsp;</TD><TD>01.08.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2015</TD><TD>&nbsp;</TD><TD>01.09.2024</TD></TR><TR><TD>1</TD><TD>&nbsp;</TD><TD>01.01.2016</TD><TD>&nbsp;</TD><TD>01.03.2024</TD></TR></TBODY></TABLE><P>Now its no problem to calculate the to number of active contracts for any given month, but I cant figure out a way to accumulate them over the year. My goal would be a table that looks like this for the current example:&nbsp;</P><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>&nbsp;</TD><TD>Counter</TD><TD>&nbsp;</TD><TD>Accumulated</TD></TR><TR><TD>01.01.2024</TD><TD>&nbsp;</TD><TD>10</TD><TD>&nbsp;</TD><TD>10</TD></TR><TR><TD>01.02.2024</TD><TD>&nbsp;</TD><TD>11</TD><TD>&nbsp;</TD><TD>21</TD></TR><TR><TD>01.03.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>34</TD></TR><TR><TD>01.04.2024</TD><TD>&nbsp;</TD><TD>12</TD><TD>&nbsp;</TD><TD>46</TD></TR><TR><TD>01.05.2024</TD><TD>&nbsp;</TD><TD>12</TD><TD>&nbsp;</TD><TD>58</TD></TR><TR><TD>01.06.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>72</TD></TR><TR><TD>01.07.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>86</TD></TR><TR><TD>01.08.2024</TD><TD>&nbsp;</TD><TD>15</TD><TD>&nbsp;</TD><TD>101</TD></TR><TR><TD>01.09.2024</TD><TD>&nbsp;</TD><TD>14</TD><TD>&nbsp;</TD><TD>115</TD></TR><TR><TD>01.10.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>128</TD></TR><TR><TD>01.11.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>141</TD></TR><TR><TD>01.12.2024</TD><TD>&nbsp;</TD><TD>13</TD><TD>&nbsp;</TD><TD>154</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I could not find an example for this specific Problem on the Forums and all solutions I found are not quite working for this situation.&nbsp;</P><P>&nbsp;</P><P>Any help or ideas would be greatly appreciated.&nbsp;</P> Wed, 03 Jul 2024 08:19:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Accumulate-Column/m-p/4022330#M158707 TKBI 2024-07-03T08:19:02Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Accumulate-Column/m-p/4022484#M158711 <P>Hi,<BR /><BR />For calculating running totals I recommend cheking this article:&nbsp;<A href="https://www.sqlbi.com/articles/computing-running-totals-in-dax/" target="_blank">Computing running totals in DAX - SQLBI</A><BR /><BR />For your case here is an example:<BR /><BR /></P> <DIV> <DIV><SPAN>Column = </SPAN></DIV> <DIV><SPAN>VAR</SPAN><SPAN> MaxDate = [Date] </SPAN><SPAN>-- Saves the last visible date</SPAN></DIV> <DIV><SPAN>RETURN</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN> (</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>('Table (39)'[Counter]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</SPAN><SPAN>-- Computes sales amount</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; 'Table (39)'[Date] &lt;= MaxDate, &nbsp; </SPAN><SPAN>-- Where date is before the last visible date</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>ALL</SPAN><SPAN> ( 'Table (39)') &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>-- Removes any other filters from Date</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV> </DIV> <P><BR />This is for a column like you requested, but I recommend using a measure. Also I recommend adding calendar table to your model.&nbsp;<BR /><BR /><BR />End result:<BR /><BR /></P> <P><img /></P> <P><BR />I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!<BR /><BR />My LinkedIn: <A href="https://www.linkedin.com/in/n%C3%A4ttiahov-00001/" target="_blank">https://www.linkedin.com/in/n%C3%A4ttiahov-00001/</A></P> Wed, 03 Jul 2024 09:02:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Accumulate-Column/m-p/4022484#M158711 ValtteriN 2024-07-03T09:02:46Z