Calculate weeks in a row with metric value of 0

[swolfe2]

I am trying to dynamically calculate the number of weeks in a row where a specific product has 0 sales.

My fact table has the following layout, which you can copy > Enter Data > paste:

Date	Week Starting	Sales	Product
1/1/2024	1/1/2024	1	A
1/2/2024	1/1/2024	1	A
1/3/2024	1/1/2024	1	A
1/4/2024	1/1/2024	1	A
1/5/2024	1/1/2024	1	A
1/6/2024	1/1/2024	1	A
1/7/2024	1/1/2024	1	A
1/8/2024	1/8/2024	0	A
1/9/2024	1/8/2024	0	A
1/10/2024	1/8/2024	0	A
1/11/2024	1/8/2024	0	A
1/12/2024	1/8/2024	0	A
1/13/2024	1/8/2024	0	A
1/14/2024	1/8/2024	0	A
1/15/2024	1/15/2024	0	A
1/16/2024	1/15/2024	0	A
1/17/2024	1/15/2024	0	A
1/18/2024	1/15/2024	0	A
1/19/2024	1/15/2024	0	A
1/20/2024	1/15/2024	0	A
1/21/2024	1/15/2024	0	A
1/22/2024	1/22/2024	1	A
1/23/2024	1/22/2024	1	A
1/24/2024	1/22/2024	1	A
1/25/2024	1/22/2024	1	A
1/26/2024	1/22/2024	1	A
1/27/2024	1/22/2024	1	A
1/28/2024	1/22/2024	1	A
1/29/2024	1/29/2024	1	A
1/30/2024	1/29/2024	1	A
1/31/2024	1/29/2024	1	A
1/1/2024	1/1/2024	1	B
1/2/2024	1/1/2024	1	B
1/3/2024	1/1/2024	1	B
1/4/2024	1/1/2024	1	B
1/5/2024	1/1/2024	1	B
1/6/2024	1/1/2024	1	B
1/7/2024	1/1/2024	1	B
1/8/2024	1/8/2024	1	B
1/9/2024	1/8/2024	1	B
1/10/2024	1/8/2024	1	B
1/11/2024	1/8/2024	1	B
1/12/2024	1/8/2024	1	B
1/13/2024	1/8/2024	1	B
1/14/2024	1/8/2024	1	B
1/15/2024	1/15/2024	0	B
1/16/2024	1/15/2024	0	B
1/17/2024	1/15/2024	0	B
1/18/2024	1/15/2024	0	B
1/19/2024	1/15/2024	0	B
1/20/2024	1/15/2024	0	B
1/21/2024	1/15/2024	0	B
1/22/2024	1/22/2024	0	B
1/23/2024	1/22/2024	0	B
1/24/2024	1/22/2024	0	B
1/25/2024	1/22/2024	0	B
1/26/2024	1/22/2024	0	B
1/27/2024	1/22/2024	0	B
1/28/2024	1/22/2024	0	B
1/29/2024	1/29/2024	0	B
1/30/2024	1/29/2024	0	B
1/31/2024	1/29/2024	0	B

I have created a DAX measure to sum sales by week, which appears to be working as expected:

Weekly Sales = CALCULATE( SUM('Fact'[Sales]), ALLEXCEPT('Fact', 'Fact'[Week Starting], 'Fact'[Product]) )

I then created another measure to calculate which weeks have 0 sales:

Is Zero Sales = IF(ISBLANK([Weekly Sales]) || [Weekly Sales] = 0, 1, 0)

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.

If there are multiple gaps of weeks in a row, I would only want to use the most recent grouping.

Any help/direction would be greatly appreciated!

[lbendlin]

This semi-additive process is very difficult to do in Power BI DAX, but relatively easy to do in Power Query.  Can the result be impacted by user filter choices?

[swolfe2]

Yes, there are multiple slicers end users will need to select from. The date range is a great example.

[lbendlin]

That makes it much more complex. Consider using visual solutions.