cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dilumd
Solution Supplier
Solution Supplier

Continuous Attendance

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 NoDate
19/17/2018
19/18/2018
19/19/2018
19/20/2018
19/21/2018
19/22/2018
19/23/2018
19/25/2018
19/26/2018
19/27/2018
19/28/2018
19/29/2018
19/30/2018
29/17/2018
29/19/2018
29/20/2018
29/21/2018
29/22/2018
29/23/2018
29/24/2018
29/25/2018
29/27/2018
29/28/2018
29/29/2018
29/30/2018
39/17/2018
39/19/2018
39/20/2018
39/21/2018
39/22/2018
39/24/2018
39/25/2018
39/27/2018
39/28/2018
39/29/2018
39/30/2018
49/17/2018
49/18/2018
49/19/2018
49/20/2018
49/21/2018
49/22/2018
49/23/2018
49/24/2018
49/25/2018
49/26/2018
49/27/2018
49/28/2018
49/29/2018
49/30/2018
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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"
    )

 cp.png


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@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"
    )

 cp.png


Regards
Zubair

Please try my custom visuals

Thank a lot @Zubair_Muhammad

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors