PowerBI Employee Time Tracking Data

[Thauser]

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.

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.

[Thauser]

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?

[amitchandak]

@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",
""
)
)