topic DAX Formula Rolling Sum when Flag is 1, however, Sum should set to 0 if continuity fails. in DAX Commands and Tips

topic DAX Formula Rolling Sum when Flag is 1, however, Sum should set to 0 if continuity fails. in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031053#M13532 <TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Flag</TD><TD> Needed Column</TD><TD> Comments</TD></TR><TR><TD>Wednesday, April 1, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 2, 2020</TD><TD>1</TD><TD>1</TD><TD> </TD></TR><TR><TD>Friday, April 3, 2020</TD><TD>1</TD><TD>2</TD><TD>increment by 1 when flg=1</TD></TR><TR><TD>Saturday, April 4, 2020</TD><TD>1</TD><TD>3</TD><TD> </TD></TR><TR><TD>Sunday, April 5, 2020</TD><TD>1</TD><TD>4</TD><TD> </TD></TR><TR><TD>Monday, April 6, 2020</TD><TD>1</TD><TD>5</TD><TD> </TD></TR><TR><TD>Wednesday, April 8, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 9, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Friday, April 10, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Saturday, April 11, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Sunday, April 12, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Monday, April 13, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Tuesday, April 14, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Wednesday, April 15, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 16, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR></TBODY></TABLE><P> </P><P>I have tried this but I would want to reset the sum to 1 on 04/10, 04/12 and so on,</P><P> </P><P>Part of it is achieved with formula for Sum Field Below; </P><DIV><DIV><SPAN>Sum = IF(Table1[Flag]=0,0,CALCULATE(SUM(Table1[Flag]),FILTER(Table1,Table1[Date] <= EARLIER(table1[Date]))))</SPAN></DIV></DIV><P> </P><TABLE><TBODY><TR><TD>Date</TD><TD>Flag</TD><TD>Sum</TD></TR><TR><TD>Wednesday, April 1, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 2, 2020</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Friday, April 3, 2020</TD><TD>1</TD><TD>2</TD></TR><TR><TD>Saturday, April 4, 2020</TD><TD>1</TD><TD>3</TD></TR><TR><TD>Sunday, April 5, 2020</TD><TD>1</TD><TD>4</TD></TR><TR><TD>Monday, April 6, 2020</TD><TD>1</TD><TD>5</TD></TR><TR><TD>Wednesday, April 8, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 9, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Friday, April 10, 2020</TD><TD>1</TD><TD>6</TD></TR><TR><TD>Saturday, April 11, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Sunday, April 12, 2020</TD><TD>1</TD><TD>7</TD></TR><TR><TD>Monday, April 13, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Tuesday, April 14, 2020</TD><TD>1</TD><TD>8</TD></TR><TR><TD>Wednesday, April 15, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 16, 2020</TD><TD>1</TD><TD>9</TD></TR></TBODY></TABLE><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV> Thu, 16 Apr 2020 22:41:27 GMT aadusumilli 2020-04-16T22:41:27Z DAX Formula Rolling Sum when Flag is 1, however, Sum should set to 0 if continuity fails. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031053#M13532 <TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Flag</TD><TD> Needed Column</TD><TD> Comments</TD></TR><TR><TD>Wednesday, April 1, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 2, 2020</TD><TD>1</TD><TD>1</TD><TD> </TD></TR><TR><TD>Friday, April 3, 2020</TD><TD>1</TD><TD>2</TD><TD>increment by 1 when flg=1</TD></TR><TR><TD>Saturday, April 4, 2020</TD><TD>1</TD><TD>3</TD><TD> </TD></TR><TR><TD>Sunday, April 5, 2020</TD><TD>1</TD><TD>4</TD><TD> </TD></TR><TR><TD>Monday, April 6, 2020</TD><TD>1</TD><TD>5</TD><TD> </TD></TR><TR><TD>Wednesday, April 8, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 9, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Friday, April 10, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Saturday, April 11, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Sunday, April 12, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Monday, April 13, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Tuesday, April 14, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR><TR><TD>Wednesday, April 15, 2020</TD><TD>0</TD><TD>0</TD><TD> </TD></TR><TR><TD>Thursday, April 16, 2020</TD><TD>1</TD><TD>1</TD><TD>New Occurance</TD></TR></TBODY></TABLE><P> </P><P>I have tried this but I would want to reset the sum to 1 on 04/10, 04/12 and so on,</P><P> </P><P>Part of it is achieved with formula for Sum Field Below; </P><DIV><DIV><SPAN>Sum = IF(Table1[Flag]=0,0,CALCULATE(SUM(Table1[Flag]),FILTER(Table1,Table1[Date] <= EARLIER(table1[Date]))))</SPAN></DIV></DIV><P> </P><TABLE><TBODY><TR><TD>Date</TD><TD>Flag</TD><TD>Sum</TD></TR><TR><TD>Wednesday, April 1, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 2, 2020</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Friday, April 3, 2020</TD><TD>1</TD><TD>2</TD></TR><TR><TD>Saturday, April 4, 2020</TD><TD>1</TD><TD>3</TD></TR><TR><TD>Sunday, April 5, 2020</TD><TD>1</TD><TD>4</TD></TR><TR><TD>Monday, April 6, 2020</TD><TD>1</TD><TD>5</TD></TR><TR><TD>Wednesday, April 8, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 9, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Friday, April 10, 2020</TD><TD>1</TD><TD>6</TD></TR><TR><TD>Saturday, April 11, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Sunday, April 12, 2020</TD><TD>1</TD><TD>7</TD></TR><TR><TD>Monday, April 13, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Tuesday, April 14, 2020</TD><TD>1</TD><TD>8</TD></TR><TR><TD>Wednesday, April 15, 2020</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Thursday, April 16, 2020</TD><TD>1</TD><TD>9</TD></TR></TBODY></TABLE><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV> Thu, 16 Apr 2020 22:41:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031053#M13532 aadusumilli 2020-04-16T22:41:27Z Re: DAX Formula Rolling Sum when Flag is 1, however, Sum should set to 0 if continuity fails. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031135#M13537 <P>Seems like Cthulhu. <A href="https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739" target="_blank">https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739</A></P> Thu, 16 Apr 2020 23:48:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031135#M13537 Greg_Deckler 2020-04-16T23:48:02Z Re: DAX Formula Rolling Sum when Flag is 1, however, Sum should set to 0 if continuity fails. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031197#M13538 <P>OMG!! this is great.</P><P> </P><P>Thanks for the quick response, Greg. it really helped.</P><P> </P><P>However, i did change a litttle on Return Part; if this helps anyone</P><P> </P><DIV><DIV><STRONG>RETURN IF(table1[Flag] = 1,IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1)),0)</STRONG></DIV></DIV> Fri, 17 Apr 2020 01:16:57 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Formula-Rolling-Sum-when-Flag-is-1-however-Sum-should-set-to/m-p/1031197#M13538 aadusumilli 2020-04-17T01:16:57Z