topic Re: Calculate weeks in a row with metric value of 0 in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3987123#M154557 <P>This semi-additive process is very difficult to do in Power BI DAX, but relatively easy to do in Power Query.&nbsp; Can the result be impacted by user filter choices?</P> Wed, 12 Jun 2024 00:39:15 GMT lbendlin 2024-06-12T00:39:15Z Calculate weeks in a row with metric value of 0 https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3986468#M154540 <P>I am trying to dynamically calculate the number of weeks in a row where a specific product has 0 sales.</P><P><BR />My fact table has the following layout, which you can copy &gt; Enter Data &gt; paste:</P><TABLE><TBODY><TR><TD><P>Date</P></TD><TD><P>Week Starting</P></TD><TD><P>Sales</P></TD><TD><P>Product</P></TD></TR><TR><TD><P>1/1/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/2/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/3/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/4/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/5/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/6/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/7/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/8/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/9/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/10/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/11/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/12/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/13/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/14/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/15/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/16/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/17/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/18/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/19/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/20/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/21/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/22/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/23/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/24/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/25/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/26/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/27/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/28/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/29/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/30/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/31/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>1</P></TD><TD><P>A</P></TD></TR><TR><TD><P>1/1/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/2/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/3/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/4/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/5/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/6/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/7/2024</P></TD><TD><P>1/1/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/8/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/9/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/10/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/11/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/12/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/13/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/14/2024</P></TD><TD><P>1/8/2024</P></TD><TD><P>1</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/15/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/16/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/17/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/18/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/19/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/20/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/21/2024</P></TD><TD><P>1/15/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/22/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/23/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/24/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/25/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/26/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/27/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/28/2024</P></TD><TD><P>1/22/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/29/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/30/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR><TR><TD><P>1/31/2024</P></TD><TD><P>1/29/2024</P></TD><TD><P>0</P></TD><TD><P>B</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I have created a DAX measure to sum sales by week, which appears to be working as expected:</P><P>&nbsp;</P><LI-CODE lang="c">Weekly Sales = CALCULATE( SUM('Fact'[Sales]), ALLEXCEPT('Fact', 'Fact'[Week Starting], 'Fact'[Product]) )</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P>I then created another measure to calculate which weeks have 0 sales:</P><P>&nbsp;</P><LI-CODE lang="markup">Is Zero Sales = IF(ISBLANK([Weekly Sales]) || [Weekly Sales] = 0, 1, 0)</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P>I am now trying to create a measure which will count the number of weeks in a row (ordered by most recent date to oldest) which can be used as a slicer value. For example, if someone wanted to filter to which products have most recently had 3 weeks of no sales, they would only see Product B.</P><P><img /></P><P><BR />If there are multiple gaps of weeks in a row, I would only want to use the most recent grouping.<BR /><BR />Any help/direction would be greatly appreciated!</P> Tue, 11 Jun 2024 15:34:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3986468#M154540 swolfe2 2024-06-11T15:34:10Z Re: Calculate weeks in a row with metric value of 0 https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3987123#M154557 <P>This semi-additive process is very difficult to do in Power BI DAX, but relatively easy to do in Power Query.&nbsp; Can the result be impacted by user filter choices?</P> Wed, 12 Jun 2024 00:39:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3987123#M154557 lbendlin 2024-06-12T00:39:15Z Re: Calculate weeks in a row with metric value of 0 https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3987320#M154566 <P>Yes, there are multiple slicers end users will need to select from. The date range is a great example.</P> Wed, 12 Jun 2024 02:37:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3987320#M154566 swolfe2 2024-06-12T02:37:06Z Re: Calculate weeks in a row with metric value of 0 https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3988516#M154587 <P>That makes it much more complex. Consider using visual solutions.</P> <P>&nbsp;</P> <P>&nbsp;</P> Wed, 12 Jun 2024 11:58:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-weeks-in-a-row-with-metric-value-of-0/m-p/3988516#M154587 lbendlin 2024-06-12T11:58:32Z