Return employees who didn't punch in & late comers list

[DivakarKrishna]

Dear Team,

I have below requirement to achieve.

1) I need to show the list of employee's who came late today (25/01/2022), let's say after 9 AM. - I will generate this report at 10 AM current day.

2) Employee list who didn't punch yet as of 10AM today  - I will generate this report at 10 AM current day.

I have two tables, Employee_master, and time_attendance_log table (This table will contains punch-in & punch-out details).

For requirement#1, I have achieved using the below measure as my visual filter.

Check Punch-In Timing =

VAR _date = TODAY()

VAR _datetime = CONCATENATE(_date," 9:00:00 AM")

return

IF (SELECTEDVALUE(PBI_WFC_Time_Attendance[Started_date]) < VALUE(_datetime),1,0)

For requiremet#2, I need to show employee list who didn't punch in for today, my fact time_attendance_log table will not contain any records when there is no punch-in our punch-out. So I am not sure how to achieve this requirement. Can some one assist me please?

[ValtteriN]

Hi,

You could filter your empolyee table based on the first condition and use this in its own filter measure. E.g.

IF(COUNTROWS(FILTER('Employees',[Check Punch-In Timing]=0))>0,1,0)


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[DivakarKrishna]

Hi @ValtteriN ,

My Employee table got only Emp ID, Emp Name, Dept fields.

All punch-in & punch-out logs will be in PBI_WFC_Time_Attendance table for each day. However, when employee didn't punch-in yet, it will not have record for that day (Example: 25/01/2022).  So specifically, i need the list where there is no record (who didn't punch-in yet) for today.

[DivakarKrishna · Accepted solution]

Dear All,

I found a way to handle this one, below DAX is working for my requirement#2.

Check Employee =

IF (

COUNTROWS(

EXCEPT(

VALUES(PBI_WFC_Emp_Master[employeeId]),

CALCULATETABLE(

VALUES(PBI_WFC_Time_Attendance[employeeId]),

USERELATIONSHIP(PBI_WFC_Emp_Master[employeeId],PBI_WFC_Time_Attendance[employeeId]),

PBI_WFC_Time_Attendance[Punch In Date]=TODAY()

)

)

)>0,1,0)

[amitchandak]

@DivakarKrishna , You can create a generic column like this and use that

new column =
var _date = date(year([PBI_WFC_Time_Attendance[Started_date]),month([PBI_WFC_Time_Attendance[Started_date]),day([PBI_WFC_Time_Attendance[Started_date]))
var _dt = _date +time (9,0,0)
return
if( PBI_WFC_Time_Attendance[Started_date] <= _dt,1,0)

[DivakarKrishna]

Hi @amitchandak ,

I need help for requirement#2, I need to show who didn't punch-in yet for today...

For requiremet#2, I need to show employee list who didn't punch in for today, my fact time_attendance_log table will not contain any records when there is no punch-in our punch-out. So I am not sure how to achieve this requirement. Can some one assist me please?

Thanks,