Count rows until value changes

[Anonymous]

Hello everybody,

I've been trying to achieve a count function in DAX for the number of days where a certain value is 0.
This count should only start if 'TODAY()' = 0 ánd
it should only count up to the first date where the value changes to >0.
Mind that in the example below only one article is shown, in practice the table contains ~15000. However, the result table will always only show 1 specific article ánd a matrix that uses the article numbers as top-level with the result of the query as column.

To illustrate this:

Date	Quantity	Article
22-02-2022	0	141
21-02-2022	0	141
20-02-2022	0	141
19-02-2022	0	141
18-02-2022	0	141
17-02-2022	1	141
16-02-2022	1	141
15-02-2022	1	141
14-02-2022	0	141

The above example should result in '5'.

I've tried a few things like:
- Using the EARLIER() function, which I have not been able to get to work.
- Using DATESBETWEEN() combined with IF() statements.

Normally I would try to post my code, but I feel like I'm so far away from the solution that it doesn't feel helpful to the post.

Kind regards,

Max

[YukiK]

This is probably what you're looking for: https://community.powerbi.com/t5/Desktop/Consecutive-Zero-Values/m-p/857111

Here is another way that's different than @DataInsights:

Count = 
VAR __ShiftedValues = 
    CALCULATE(
        MIN('Table'[Quantity] ),
        TOPN(
            1,
            FILTER( 
                ALLEXCEPT('Table','Table'[Article] ),
                'Table'[Date] > MIN('Table'[Date])
            ),
            'Table'[Date],
            ASC
        )
    )
VAR __CurrQuantity = SELECTEDVALUE( 'Table'[Quantity] ) 
VAR __Cnt = 
    IF( __ShiftedValues = 0 && __CurrQuantity = 0 , 1, 0 )
 
RETURN 
__Cnt

Please give it thumbs up if this helps!

[DataInsights · Accepted solution]

@Anonymous,

Try this measure:

Count Measure = 
VAR vToday =
    TODAY ()
VAR vTodayQuantity =
    CALCULATE (
        SUM ( Table1[Quantity] ),
        ALLEXCEPT ( Table1, Table1[Article] ),
        Table1[Date] = vToday
    )
VAR vFirstDayNonZero =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[Article] ),
        Table1[Quantity] > 0
    )
VAR vRowCount =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[Article] ),
        Table1[Date] > vFirstDayNonZero,
        Table1[Date] <= vToday
    )
VAR vResult =
    IF ( vTodayQuantity = 0, vRowCount )
RETURN
    vResult

[Anonymous]

Works like a charm, thankyou!