Consecutive Working Days

Hi, I am trying to calculate the number of consecutive working days from current date/day based off the working hours column - if they have recorded time in here or it reads 0/null. Example ...

Need Help

Anonymous

3 years ago

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).