Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 3
Phil_Seamark
Employee
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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark thanks for the replay.

 

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

Please suggest me.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.