Calculating Consecutive Days Worked by Staff

[Anonymous]

Hi,

I'm currently stuck trying to create a measure that will calculate the number of consecutive days worked by staff across a given period of time (date is an adjustable filter, so people can see days worked within a chosen time frame).

I've seen a few posts on this matter, but none seem to work for me.

Sample Data;

Date	Staff ID	Hours Worked
01/01/2019	abc	1
02/01/2019	abc	5
03/01/2019	abc	1
04/01/2019	abc	4
05/01/2019	abc	1
06/01/2019	abc
07/01/2019	abc
08/01/2019	abc	1
09/01/2019	abc	5
10/01/2019	abc	1
11/01/2019	abc	5
12/01/2019	abc	1
13/01/2019	abc	4
14/01/2019	abc	1
15/01/2019	abc	1
16/01/2019	abc	5
17/01/2019	abc	1
18/01/2019	abc	4
19/01/2019	abc	1
20/01/2019	abc
21/01/2019	abc
22/01/2019	abc	1
23/01/2019	abc	5
24/01/2019	abc	1
25/01/2019	abc	4
26/01/2019	abc	1
27/01/2019	abc
28/01/2019	abc

How I want this measure to work is if I select a date range 03/01/2019 - 07/01/2019, I would get an answer of 3 consecutive days worked (this person has hours on 03/01-05/01). If he has no hours, it is assumed that he has not worked and so breaks the cycle. If the date range was 03/01/2019 - 09/01/2019, the cumulative day count would still be 3 as this is the highest number of consecutive days in that range.

Now if the date range is 08/01/2019 - 19/01/2019, I would expect the measure to tell me that this person has worked 12 consecutive days (he has hours for each day within that range, so he has worked). In addition to this, if the date range is selected as 12/01/2019 - 19/01/2019, the return would be 8 consecutive days.

It has to be noted that the data set is quite large and includes many staff so needs to factor this in when giving a return by employee.

I would really appreciate any help on this, as this is driving me crazy trying to figure this out.

Thanks.

[v-joesh-msft]

Hi @Anonymous ,

I am not sure if it is the result you want, you can try the measure below:

consecutive days =
VAR t =
    ADDCOLUMNS (
        ADDCOLUMNS (
            'Table',
            "LastRestDay",
            VAR currentday = [Date]
            VAR result =
                CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( 'Table', [Hours Worked] + 0 = 0 && 'Table'[Date] < currentday )
                )
            RETURN
                IF (
                    result = BLANK (),
                    CALCULATE ( MIN ( 'Table'[Date] ), 'Table' ) - 1,
                    result
                )
        ),
        "cDay", IF ( [Hours Worked] = 0, 0, DATEDIFF ( [LastRestDay], [Date], DAY ) )
    )
RETURN
    MAXX ( t, [cDay] )

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EavYwoMQH8tLqRFXCDYz9_UB9I0dj0YDiMKOM1x8vm1o7g?e=AiT7ed

Best Regards,

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

[YJ]

i would like to know how this works too, thanks