Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 )
Proud to be a Super User!
Might be a bit late, but this blog post solves the issue of calculating time within office hours 🙂
Posting it here to help others in the future.
https://www.villezekeviking.com/calculating-handling-time-during-office-hours/
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 )
Proud to be a Super User!
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
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.
Once all worked fine, please kindly mark @AnthonyTilley 's solution as answer to help others find it more quickly.
Thanks!
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 🙂
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
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |