How to show employees on the clock in 10 minute increments??

[zraptor]

Hello experts!

I have a problem I am struggling with.  I have a table (below) that shows employee names along with their clockin, begin break, end break, and clockout times.  I am trying to illustrate the number of people on the clock at any given time broken into 10 minute segments.  I've been able to show this as a running total with a DAX measure but what the measure doesn't do is subtract from the total when someone has clocked out.  Can anyone tell me how to accomplish this??  I feel like I am missing something too easy.

Table example-

Name	Clock in	Begin Break	End Break	Clock out
John B	2022-01-02 12:00:00	2022-01-02 13:00:00	2022-01-02 14:00:00	2022-01-02 20:00:00
Ashley T	2022-01-02 13:00:00	2022-01-02 16:00:00	2022-01-02 17:00:00	2022-01-02 22:12:00
Adam W	2022-01-02 15:00:00	2022-01-02 18:00:00	2022-01-02 19:00:00	2022-01-03 00:20:00

My desired result would be a graph where along the X axis would be times in 10 minute increments and the Y axis would be the number of employees on the clock at that time window.

[zraptor]

@FreemanZ , can I ask one quick question as followup?  What could I do IF an employee started at 10pm and clocked out at 8am?  Basically when their shift crosses midnight into the next day?

[FreemanZ]

hi @zraptor 

as it is counted every 10 minutes, i don't see the issue. Just try and see if any adjustment is needed.

[zraptor]

I am looking at the data table and there are employees that worked during the PM hours and for some reason they dont show at all.  It's weird.  I'll play around with it some more and see what I can come up with.  Thank you again!  You are a life saver!

[zraptor]

@FreemanZ , please disregard my question regarding those working over midnight.  I found out why they werent showing, I had a filter on.  Apologies for wasting your time on this part!  Take care!

[FreemanZ]

hi @zraptor 

to realize the issue and fine tune a solution by oneself is always the best possible outcome. Take care and Enjoy

[zraptor]

@FreemanZ This is exactly what I needed!  Thank you so much!  I was using a measure like below and it just wasn't working right.  

CALCULATE(
	SUM('Data'[EMP Count]),
	FILTER(
		ALLSELECTED('Data'[Clockin time]),
		ISONORAFTER('Data'[Clockin time], MAX('Data'[Clockin time]), DESC)
	),userelationship('10MIN'[10MINSpan],Data[Clockin time])
)

[FreemanZ · Accepted solution]

hi @zraptor 

Not sure if i fully get you, tried the following:

0) modified your data sample to better illustrate the chart as:
NameClock InBegin BreakEnd BreakClock Out

A	1/2/2022 12:00:00 PM	1/2/2022 12:10:00 PM	1/2/2022 12:20:00 PM	1/2/2022 12:30:00 PM
B	1/2/2022 12:10:00 PM	1/2/2022 12:20:00 PM	1/2/2022 12:30:00 PM	1/2/2022 12:40:00 PM
C	1/2/2022 12:20:00 PM	1/2/2022 12:30:00 PM	1/2/2022 12:40:00 PM	1/2/2022 12:50:00 PM
D	1/2/2022 12:30:00 PM	1/2/2022 12:40:00 PM	1/2/2022 12:50:00 PM	1/2/2022 1:00:00 PM
E	1/2/2022 12:40:00 PM	1/2/2022 12:50:00 PM	1/2/2022 1:00:00 PM	1/2/2022 1:10:00 PM
F	1/2/2022 12:50:00 PM	1/2/2022 1:00:00 PM	1/2/2022 1:10:00 PM	1/2/2022 1:20:00 PM

2) add a time table like:

DateTime

1/2/2022 12:00:00 PM

1/2/2022 12:10:00 PM

1/2/2022 12:20:00 PM

1/2/2022 12:30:00 PM

1/2/2022 12:40:00 PM

1/2/2022 12:50:00 PM

1/2/2022 1:00:00 PM

1/2/2022 1:10:00 PM

1/2/2022 1:20:00 PM

3) plot the TimeTable[DateTime] column (the column itself not the hierarchy) with a measure like:

OnClockCount = 
VAR _time = MAX(TimeTable[DateTime])
RETURN
COUNTROWS(
    FILTER(
        TableName, 
        TableName[Clock in]<=_time
            &&TableName[Clock out]>=_time + TIME(0, 10, 0)
    )
)

it worked like this: