Count value in a column and break by a value and count again like a loop

[Phuonghm22]

I have a sample like this table. The column count of 2 is the output I expect. With the same ID, order Date ascending. We count number 2 (or rank) by Date. If the value in column A is 1, we break the loop and count (or rank) the number 2 again from the beginning.

Plz help me. Thank you

[v-jialluo-msft · Accepted solution]

^{Hi Phuonghm22,}

^{Please follow these steps:}

^{(1) Create a calculated column to count}

Co2 =
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[A] <> 2
                && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    ) + 0
RETURN
    _count

^{(2) Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Phuonghm22]

Thank you so much

[amitchandak]

@Phuonghm22 , I think it is case of

Continuous streak : https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...