Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 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/2022100111:21
17/02/2022100116:18
18/02/2022100114:11
19/02/2022100110:23
20/02/2022100107:46
21/02/2022100100:00
22/02/2022

1001

 
23/02/2022100111:21
24/02/2022100111:41
25/02/2022100115:31
26/02/2022100110:57
27/02/2022100108:57
28/02/20221001 

 

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

 

Thanks

Chris

Need Help
  • Anonymous's avatar
    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).

    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.           

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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).

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Awesome, thanks. 

      Really appreciate your help.

       

      Chris