Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculate Total swipe Intime

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 NameDatetimeIn/Out
A5/31/20188:35:08 AMI01
A5/31/20185:31:48 PMO01
B5/31/20187:16:30 AMI01
B5/31/20189:51:47 AMO01
C5/31/201812:17:47 AMI01
C5/31/20186:40:02 PMO01
D5/31/20186:51:05 AMI01
D5/31/20181:37:59 PMO01

 

 

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.

 

 

 

 

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Phil_Seamark thanks for the replay.

       

      Yes.. need to show the employees and thier total hour spent. 

      Please suggest me.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Phil_Seamark expected output for the given sample data is.

         

        Emp NameOutput In Hr.Min
        A9.4
        B2.35
        C6.23
        D6.46

         

        and Yes.. need to show the employees and thier total hour spent in a daily basis.

         

        Please suggest.

         

        Thanks,

        Mohan V