cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HxH Regular Visitor
Regular Visitor

Calculating time in office

Hy guys, 
I have a table which records every time an employee enters and exits the office via badge identification. I want to calculate the time spent working for each employee, and I want to do it by subtracting the "time outside of the office" (the difference between a badge-in and the previous badge-out) from the total time worked each day (which is the difference between the MIN badge-in and the MAX badge-out of the day), The reason I want to do it this way is that many times employees forget to badge-out of the office and then do another badge-in when they come back, therefore I get multiple consecutive badge-in that I don't want to consider. 

The columns of the table are: 
-User ID 
-Date 
-Time (time of badge-in or badge-out) 
-badge-in/badge-out

DATA EXAMPLE:
UserID   Date     Time   Badge
A123   12/8/19   8.00    IN
A123   12/8/19   9.30    OUT 
A123   12/8/19   10.00  IN
A123   12/8/19   10.30  IN
A123   12/8/19   11.00  IN
A123   12/8/19   12.00  OUT

For this day and this employee the time outside of office is 30minutes and therefore the total time worked (which will be a measure I think) will be 3.30 hours.

I've been trying to do this for a couple days and I'm stuck, any help would be appreciated 
Thank 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
AnthonyTilley Established Member
Established Member

Re: Calculating time in office

not sure if this is the best way but it worked for me.

 

first of all you need a date time colunm so combine your date & time colunms intoone (you still need the separte colunms so do not merge use a calculated colunm )

 

datetime = 'Table'[Date]+'Table'[Time]
 
next you need to get a colunm that will give the difference in time for each out colunm to its next in colunm

OUTTIME =
--get userid for row
var i = 'Table'[UserID ]
--get date for row
var d = 'Table'[Date]
--get status
var mb = 'Table'[Badge]
--get current datetime
var curtime = 'Table'[datetime]
--calculate the next date stamp that is after the current out stamp and is in the same day, user id and where badge is IN
var nexttime = if(mb = "OUT", CALCULATE(MIN('Table'[datetime]),all('Table'),'Table'[datetime]>curtime,'Table'[UserID ] = i ,'Table'[Date] = d,
'Table'[Badge]="IN"
))
--get diffrfence between the two datetime stamps in mins to give mins of out time
var outtime = DATEDIFF(curtime,nexttime,MINUTE)
--return the cvalue in mins
RETURN outtime
 
colunm.png

this colunm will be used later to sum to get the total out time for each user in a day 
 
next you need a measure for the total time (effectivly the diffrence between the minimum IN stamp and the Maximum OUT stamp)
 
TOTALTIME =
var i = MAX('Table'[UserID ])
var d = max('Table'[Date])
var MinIn = CALCULATE(min('Table'[datetime]),ALL('Table'),'Table'[Badge]="IN",'Table'[UserID ] = i ,'Table'[Date] = d)
var MaxOut = CALCULATE(MAX('Table'[datetime]),ALL('Table'),'Table'[Badge]="OUT",'Table'[UserID ] = i ,'Table'[Date] = d)
var totaltime = DATEDIFF(MinIn,MaxOut,MINUTE)

return totaltime
finally create a masure to subtract one from the other 
timeIn = [TOTALTIME]-sum('Table'[OUTTIME])
 
this will give you what you are after if you add it to a matrix split by userid and date
 
table.png
 
you can convert the mins to time by wrapping the masure in a time function
=TIME(0,([TOTALTIME]-sum('Table'[OUTTIME])),0)
 
6 REPLIES 6
Highlighted
AnthonyTilley Established Member
Established Member

Re: Calculating time in office

not sure if this is the best way but it worked for me.

 

first of all you need a date time colunm so combine your date & time colunms intoone (you still need the separte colunms so do not merge use a calculated colunm )

 

datetime = 'Table'[Date]+'Table'[Time]
 
next you need to get a colunm that will give the difference in time for each out colunm to its next in colunm

OUTTIME =
--get userid for row
var i = 'Table'[UserID ]
--get date for row
var d = 'Table'[Date]
--get status
var mb = 'Table'[Badge]
--get current datetime
var curtime = 'Table'[datetime]
--calculate the next date stamp that is after the current out stamp and is in the same day, user id and where badge is IN
var nexttime = if(mb = "OUT", CALCULATE(MIN('Table'[datetime]),all('Table'),'Table'[datetime]>curtime,'Table'[UserID ] = i ,'Table'[Date] = d,
'Table'[Badge]="IN"
))
--get diffrfence between the two datetime stamps in mins to give mins of out time
var outtime = DATEDIFF(curtime,nexttime,MINUTE)
--return the cvalue in mins
RETURN outtime
 
colunm.png

this colunm will be used later to sum to get the total out time for each user in a day 
 
next you need a measure for the total time (effectivly the diffrence between the minimum IN stamp and the Maximum OUT stamp)
 
TOTALTIME =
var i = MAX('Table'[UserID ])
var d = max('Table'[Date])
var MinIn = CALCULATE(min('Table'[datetime]),ALL('Table'),'Table'[Badge]="IN",'Table'[UserID ] = i ,'Table'[Date] = d)
var MaxOut = CALCULATE(MAX('Table'[datetime]),ALL('Table'),'Table'[Badge]="OUT",'Table'[UserID ] = i ,'Table'[Date] = d)
var totaltime = DATEDIFF(MinIn,MaxOut,MINUTE)

return totaltime
finally create a masure to subtract one from the other 
timeIn = [TOTALTIME]-sum('Table'[OUTTIME])
 
this will give you what you are after if you add it to a matrix split by userid and date
 
table.png
 
you can convert the mins to time by wrapping the masure in a time function
=TIME(0,([TOTALTIME]-sum('Table'[OUTTIME])),0)
 
AnthonyTilley Established Member
Established Member

Re: Calculating time in office

one note is that this will only work if the final stamp is always an out.

if the final stamp is an IN then the value will be wrong becuase the total will only calculate up to the final OUT

but the Outtime that is subtracted will calculate an amount against the final out 

 

for example 

 

if there where only three entries for the day 

In - 9:00

In - 10:00

Out - 11:00

this would cacluate as out time 0

total time 120 (2 hours)

in time 120 - 0 = 120

 

if however the user then logged in again in the same day without loggi back out 

In - 9:00

In - 10:00

Out - 11:00

IN - 12:00

then the outtime would be 60 (1 hour)

the total time would be 120 (no chnage as max out is still the same)

so the timein would be 120 - 60 

 

this can be corrected by eaither ensuring the final stamp is alwasy an out 

or by removing the filter criteria ,'Table'[Badge]="OUT" from var MaxOut in the totaltime measure 

HxH Regular Visitor
Regular Visitor

Re: Calculating time in office

I think I did something very similar (if not the same) in one of my trials and I keep getting a message error saying that I don't have enough memory to create the calculated column with the outtime, which is stupid since it is only a 100k rows table, not some billions rows table. I hoped in a different solution to work around the problem but it seems I will have to solve this memory problem because this is the only way to perform the task I guess 

Thanks anyway it was very helpful 

Community Support Team
Community Support Team

Re: Calculating time in office

Hi @HxH ,

 

 For the out of memory error, Calculated column (especially in Fact Table) is going to impact performance of model.For optimized data analysis, you should denormalize your data. Meaning transformation from schema that's optimized for data entry to schema that's optimized for reporting.  Perhaps transforming data in query stage may help in performance. 

 

 Also, if you connect the data in import model, please disable "Allow data preview to download in the background" from options / options and settings/data load.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Calculating time in office

Once all worked fine, please kindly mark @AnthonyTilley 's solution as  answer to help others find it more quickly.

Thanks!

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HxH Regular Visitor
Regular Visitor

Re: Calculating time in office

I marked the answer as solution since I am absolutely sure it is the way to perform the calculation. I still have the memory problem, I'll open another topic if I can't solve it. Thanks to both Smiley Happy

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 377 members 3,668 guests
Please welcome our newest community members: