Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count rows until value changes

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:

DateQuantityArticle
22-02-2022     0141
21-02-2022     0141
20-02-2022     0141
19-02-2022     0141
18-02-2022     0141
17-02-2022     1141
16-02-2022     1141
15-02-2022     1141
14-02-2022     0141

 

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

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Works like a charm, thankyou!

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    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!