Time Sheet Tracking - Early, On Time or Late

[ClaireBear]

Hi, 

i have a list of multiple customers with employees that all start and end at different times (by company/site). I have calculated a measure to work out the average start time and the average end time, based off clock in time and clock out time.

Avg. Start Time =

VAR Result =

AVERAGEX ( VALUES ( 'Date'[Date] ), CALCULATE ( MIN ( 'Employee'[ClockInTime] ) ) )

RETURN

IF ( NOT ( ISBLANK ( Result ) ), Result + TIME ( 0, 0, 0 ) )

My question is, how do i apply a calculation to tell me how many employees are early, on time or late based off their Clock In and Clock Out times by company/Site? The Site is a drop down so the table below should update depending on what Site is selected. 

Required Result:

Example Data:

 Thanks

[ClaireBear]

ok thanks, i was hoping that the average clock in and clock out time could be used as a benchmark against each employee's clock in time as i dont have this info. 

[Anonymous]

@ClaireBear 

Don't despair 🙂 I can see you want something like a dynamic labelling. It's totally doable but you still have to define what you mean by Early, Late... For instance, does Early mean "before the average"? Or "more than 10 minutes before the average"? And is Late "20+ mins after the average"? You have to define this.

[Anonymous]

Not enough info to solve this. For instance, nobody knows (but you) what the definitions of Early, On Time and Late are.

By the way, your formula does too many redundant things. If you exec this in DAX Studio

EVALUATE
var Time_ = TIME(20, 2, 1)
return
    {Time_, Time_ + time(0, 0, 0)}

you'll notice that the results are exactly the same. Hence, your IF is redundant.