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 ...
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)
     
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.

       

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

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

        Thanks!