Consecutive Dates with data

Hi, karo 

Based on your scenario, I made a simple example to count consecutive absence days.You can further filter the data based on the newly fields 'Max of Consecutive absent' .

Related codes of calculated columns:

flag = IF('Table1'[Statue]="absent",1,0) 

Consecutive absent =
SWITCH (
    TRUE (),
    Table1[flag] = 0, 0,
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[flag] = 0
                && Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date] < EARLIER ( Table1[Date] )
        )
    ) = 0,
        CALCULATE (
            SUM ( Table1[flag] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Name] = EARLIER ( Table1[Name] )
                    && Table1[Date] <= EARLIER ( Table1[Date] )
            )
        ),
    CALCULATE (
        SUM ( Table1[flag] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Name] = EARLIER ( Table1[Name] )
                && Table1[Date]
                    > CALCULATE (
                        MAX ( Table1[Date] ),
                        FILTER (
                            ALL ( Table1 ),
                            Table1[flag] = 0
                                && Table1[Name] = EARLIEST ( Table1[Name] )
                                && Table1[Date] < EARLIEST ( Table1[Date] )
                        )
                    )
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    )
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.