topic Re: Employees Stamp in DAX Commands and Tips

Employees Stamp

Luca2020 — Mon, 09 Jan 2023 13:44:01 GMT

Hi,

I have a table with the daily entry and exit records for each employee.

empl_id	date	type
1036	29/12/2022 14:08	IN
1036	29/12/2022 22:08	OUT
1036	31/12/2022 07:21	IN
1036	31/12/2022 12:44	OUT

I would prefer to have a table with the entry and exit records as different columns instead.

empl_id	entry	exit	h
1036	29/12/2022 14:08	29/12/2022 22:08	8
1036	31/12/2022 07:21	31/12/2022 12:44	5

Some employees work at night so they start one day and finish the next one.

Can someone help me?

Thanks

Re: Employees Stamp

tamerj1 — Mon, 09 Jan 2023 14:34:29 GMT

Hi Luca2020

Do you have double punches or your data is just as clean as this sample?

Re: Employees Stamp

Luca2020 — Mon, 09 Jan 2023 14:49:18 GMT

This is my database,

I have all the daily In and Out records for each employee,

the difficult part is when workers start one day and finish the next one, and start a new shift later on the same day.

Re: Employees Stamp

tamerj1 — Mon, 09 Jan 2023 15:46:06 GMT

Luca2020
Please try the following query.

Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.

AttendanceNew = VAR T1 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ), "Emp.No.", AttendanceSource[empl_id], "@Date", DATEVALUE ( AttendanceSource[datd] ), "ClockIn", AttendanceSource[data] ) VAR T2 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ), "Emp.No.1", AttendanceSource[empl_id], "ClockOut", AttendanceSource[data] ) VAR T3 = GENERATE ( T1, VAR TimeIn = [ClockIn] VAR DateIn = DATEVALUE ( TimeIn ) VAR T4 = FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn ) VAR T5 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn ) VAR T6 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 ) VAR ClockOut1 = MAXX ( T5, [ClockOut] ) VAR ClockOut2 = MINX ( T6, [ClockOut] ) VAR ClockOut = COALESCE ( ClockOut1, ClockOut2 ) RETURN ROW ( "ClockOut", ClockOut ) ) RETURN T3