Forum Discussion

HxH's avatar
HxH
Icon for Advocate II rankAdvocate 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)
     
v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity 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
Icon for Community Support rankCommunity Support
7 years ago

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

Thanks!

 

 

  • HxH's avatar
    HxH
    Icon for Advocate II rankAdvocate II
    7 years ago

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