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.
