Continuous Attendance

[dilumd]

Hi Everyone,

I have below table with employee attendance data from which I need to identify whether there is any employee who has reported to work continuously for 7 days or more. Pls help, may data look like this. Thank you inadvance !

EMP No	Date
1	9/17/2018
1	9/18/2018
1	9/19/2018
1	9/20/2018
1	9/21/2018
1	9/22/2018
1	9/23/2018
1	9/25/2018
1	9/26/2018
1	9/27/2018
1	9/28/2018
1	9/29/2018
1	9/30/2018
2	9/17/2018
2	9/19/2018
2	9/20/2018
2	9/21/2018
2	9/22/2018
2	9/23/2018
2	9/24/2018
2	9/25/2018
2	9/27/2018
2	9/28/2018
2	9/29/2018
2	9/30/2018
3	9/17/2018
3	9/19/2018
3	9/20/2018
3	9/21/2018
3	9/22/2018
3	9/24/2018
3	9/25/2018
3	9/27/2018
3	9/28/2018
3	9/29/2018
3	9/30/2018
4	9/17/2018
4	9/18/2018
4	9/19/2018
4	9/20/2018
4	9/21/2018
4	9/22/2018
4	9/23/2018
4	9/24/2018
4	9/25/2018
4	9/26/2018
4	9/27/2018
4	9/28/2018
4	9/29/2018
4	9/30/2018

[Zubair_Muhammad · Accepted solution]

@dilumd

Try this calculated column.

 It will identify the first day when consecutive 7 days start for each employee

You can then use it as VISUAL filter

Continuously_Present =
VAR mycontinuousdays = 7
RETURN
    IF (
        [Date] + mycontinuousdays
            - 1
            = CALCULATE (
                MIN ( [Date] ),
                FILTER (
                    Table1,
                    [EMP No] = EARLIER ( [EMP No] )
                        && [Index]
                            = EARLIER ( [Index] ) + mycontinuousdays
                                - 1
                )
            ),
        "Present"
    )

 

[dilumd]

Thank a lot @Zubair_Muhammad