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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Thauser
Regular Visitor

PowerBI Employee Time Tracking Data

Hello, I have been working on a PowerBI report that is intended to tack our student employees time versus their schedules. I have run into several issues, and could use some help. Here is the background we have a legacy scheduler system and use workday for our time tracking. Because of this there is no shift id or anything that can relate the two tables.

 

Thauser_0-1686082113654.png

Both Tables have employee id, start time, end time, and date. I need need to be able to determine the following

1. If a student was on time,

2. If a student was tardy

3. If a student was absent.

4. If a student has mismatched scans. (i.e. they scanned in when they should have scanned out or vice versa)

 

Here are some additonal caveats each student can work multiple shifts per day, and those shifts could include a break.  Any help with this would be apprecaited.

2 REPLIES 2
Thauser
Regular Visitor

Here is examples of the tables.  The first one is actual scans. and the second one is scheduled shifts. I am not sure how to share the PBIX file. The desired output would be a table that lists each shift, and whether that shift was worked, missed, or abscent. The biggest problem is that the students can have multiple shifts with multiple breaks per day. How can i write a formula that checks all of the scans against the each schedule, and will it work ok or be to slow?

Thauser_0-1686142790062.png

Thauser_1-1686142969105.png

 

 

amitchandak
Super User
Super User

@Thauser ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

One of the column you need will be

 

Attendance Status =
_Schstrat =maxx(filter(Employee, datevalelue(Employee[Actual Start Time]) = datevalelue(Schedule[Scheduled Start Time])),[Scheduled Start Time])
IF(
Employee[Actual Start Time] >= _Schstrat ,
IF(
Employee[Actual Start Time] <= _Schstrat + TIME(0, 15, 0),
"On Time",
"Tardy"
),
"Absent"
)

 

Scan status =
if(
isblank([actual end time] ) && [actual start time] <> blank(),
"mismatched scan out",
if(
[actual end time] <> blank() && isblank([actual start time]),
"mismatched scan in",
""
)
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors