Consecutive Working Days

Hi Anonymous ,

Here's the measure which returns the result.

Consecutive Working Days =
VAR _today =
    TODAY()
VAR _lastblank =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ID (Text)] = MAX ( 'Table'[ID (Text)] )
                && [Date (Date)] < _today
                && [Working Hours (Time)] = BLANK ()
        ),
        [Date (Date)]
    )
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ID (Text)] = MAX ( 'Table'[ID (Text)] )
                && [Date (Date)] > _lastblank
                && [Date (Date)] < _today
        )
    )

To illustrate the expected outcome, I replaced TODAY() with DATE(2022,2,28).

Here's the result. For ID 1001, the consecutive working days is 5 and for ID 1002 is 2.

Best Regards,

Stephen Tao

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