How to calculate hours worked for each day based on first time in minus last time out?

[rush]

I currently have a table called acc_monitor_log with columns:

• time (logs the time when you enter a door & when you exit),
• device_name (Fire Escape IN, Fire Escape OUT, Front Door IN, Front Door OUT, GTC Office IN, GTC Office Out)
• pin (which relates to the user)

I would like to calculate the hours worked for each day of every month based on the first time they entered for work & the last time, irrespective of the door they entrered or exited.

[Anonymous]

Hi @rush,

According to your description, you want to calculate the daily work hours and get the monthly total,right?

If this is a case, you can refer to below steps:

1. Add a calcuale column to calculate the daily work hours.

Daily Diff = 
var temp= FILTER(Table,[Time].[Date]=EARLIER(Table[Time].[Date])&&[Pin]=EARLIER([Pin])&&[device_name]=EARLIER([device_name]))
return
DATEDIFF(MINX(temp,[Time]),MAXX(temp,[Time]),MINUTE)

2. Summary above table with same month.

Summary = SUMMARIZE(Table,[Pin],[device_name],[Time].[Month],"Total Minute",SUM(Time[Daily Diff]),"Total Hour",SUM(Time[Daily Diff])/60)

Regards,

Xiaoxin Sheng