Forum Discussion

HxH's avatar
HxH
Advocate II
7 years ago
Solved

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 

Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    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)
     

7 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    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)
     
    • AnthonyTilley's avatar
      AnthonyTilley
      Solution Sage
      7 years ago

      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's avatar
      HxH
      Advocate II
      7 years ago

      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 

      • v-diye-msft's avatar
        v-diye-msft
        Community Support
        7 years ago

        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.