creating a measure to count employees within a time period

[Anonymous]

Hi, I am trying to create a measure which countshow many employees are rostered within a 24hr period.  I am able to get it to work if it is between 00:00-23:59 (eg. 06:00-14:00), but not if it cuts over to the next day (eg.2200-0600).

I have created a Timetable where I have generated times between 00:00 and 23:59 in 30min increments

Timetable = GENERATESERIES(TIMEVALUE("00:00"), TIMEVALUE("23:59"), TIME(0,30,0))

I have also created a min and max value measure min = 00:00 max = 23:59

My measure to count the employees that are rostered on between these times is:

EmployeeCount = CALCULATE(DISTINCTCOUNT(EmployeeDataWorkPattern[Employee ID]),

FILTER(EmployeeDataWorkPattern, and(EmployeeDataWorkPattern[Start Time]<=MIN(Timetable[Value]), EmployeeDataWorkPattern[End Time])>MAX(Timetable[Value])))

Is there anyone that can help with this?

[PhilipTreacy]

Hi @Anonymous 

If you have periods that extend past midnight then this approach won't work. You need to be able to distinguish between different days, typically by using DateTimes not just the time.

Can you please share some example data.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy 

would the below be okay?

I would ultimately like to be able to create a line graph and analyse the roster hours that are worked over each day of the week over a 24hr period.  I was worried that the date/time formula might not allow me to do this. 

If it can work with the date/time, I have added a column for the date/time on the right hand side to get things started but will need help to get the formula for the shift end date/time.

Thankyou!