Consecutive Working Days

[Anonymous]

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 data set would be - with todays date being 28/02/2022 for example

Date (Date)	ID (Text)	Working Hours (Time)
15/02/2022	1001	01:54
16/02/2022	1001	11:21
17/02/2022	1001	16:18
18/02/2022	1001	14:11
19/02/2022	1001	10:23
20/02/2022	1001	07:46
21/02/2022	1001	00:00
22/02/2022	1001
23/02/2022	1001	11:21
24/02/2022	1001	11:41
25/02/2022	1001	15:31
26/02/2022	1001	10:57
27/02/2022	1001	08:57
28/02/2022	1001

Expected output for KPI card would be 5 consecutive working days. 

Thanks

Chris

[Anonymous · Accepted solution]

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.           

[Anonymous]

Awesome, thanks. 

Really appreciate your help.

Chris

[Greg_Deckler]

@Anonymous Try Cthulhu - Cthulhu - Microsoft Power BI Community