Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table which having employees swiped in and swiped out data.
I am trying to calculate the total time that each employee has stayed on premises.
But as per my organization rule, they want to calculate the employees total spend time in office
between first IN should be >=7:00:00 AM(Today) and last OUT should be <=6:59:59 AM(Next Day)
That means for each employee in our organization the day starts from 7:00 AM of CurrentDay and Ends on 6:59:59 AM of next day.
Sample Data:-
Emp Name | Date | time | In/Out |
A | 5/31/2018 | 8:35:08 AM | I01 |
A | 5/31/2018 | 5:31:48 PM | O01 |
B | 5/31/2018 | 7:16:30 AM | I01 |
B | 5/31/2018 | 9:51:47 AM | O01 |
C | 5/31/2018 | 12:17:47 AM | I01 |
C | 5/31/2018 | 6:40:02 PM | O01 |
D | 5/31/2018 | 6:51:05 AM | I01 |
D | 5/31/2018 | 1:37:59 PM | O01 |
Now in above table, there are some scenarios are like,
For A and B the conditions that i have mentioned above are satisfied.
For C the first IN is at 12:17:47 AM and Last Out is at 6:40:02 PM
but as i mentioned above that the employee swipe in date starts from 7 AM(Currentday) to 6:59AM(next day)
then for C the first IN is at 12:17 but as per condtion it wont have the last out as day ends at 6:59AM,
so here we have to take the last out as 6:59AM.
and again after 6:59 as day ends here, but employee still in office then we wont have a first in for the day.
so here we have to take 7:00AM as first in by default and calculate the total no of hours spent.
How can we get this in powerbi.
Any help please.
Mohan V.
HI @Anonymous
Based on your sample data, what would your expected output look like? Do you just want a table that shows Employees and then their total hour count? Or does this need to be broken down by day?
@Phil_Seamark thanks for the replay.
Yes.. need to show the employees and thier total hour spent.
Please suggest me.
@Phil_Seamark expected output for the given sample data is.
Emp Name | Output In Hr.Min |
A | 9.4 |
B | 2.35 |
C | 6.23 |
D | 6.46 |
and Yes.. need to show the employees and thier total hour spent in a daily basis.
Please suggest.
Thanks,
Mohan V
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |