calculating the consecutive count

[YogeshS]

Hello,

I am trying to calculate the consecutive absent count of students......like they should be absent for 7 days continuously then the dax should take the count as 1 and if another student is asbsent for continuous 7 days then, dax should increase its count to 2. I have attached the above picture for reference. Consider Dates are stacked in one single column,  in the above picture i divided the date into columns for easy understanding.
Help me to create a measure/ calculated column.

[YogeshS]

https://drive.google.com/file/d/1ul3CV3LSPBy5gk8tfmDtrunqCu-eKOsj/view?usp=drive_link
The .Pbix is in the above url

[YogeshS]

Hi @v-jianboli-msft ,

Thank you for your help,
But my data seems to be big, i am getting the below error after implementing your measure.

Could you please help me with this?

[v-jianboli-msft]

Hi @YogeshS ,

Please try:

Measure =
VAR _a =
    COUNTX ( FILTER ( 'Table', [Value] = 0 ), [Date] )
VAR _b =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            FILTER ( ALL ( 'Table' ), [ID] <= MAX ( 'Table'[ID] ) ),
            COUNTX (
                FILTER ( ALL ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) && [Value] = 0 ),
                [Date]
            ) = 7
        )
    )
RETURN
    IF ( _a = 7, _b )

Final output:

Best Regards,

Jianbo Li

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