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

 

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

 

 

you can convert the mins to time by wrapping the masure in a time function

=TIME(0,([TOTALTIME]-sum('Table'[OUTTIME])),0)