Schedules of employees

[tagban]

I tried to dig a little to find some answers and maybe my brain is just fried so I wanted to ask here to see if anyone had any experience. We have a little over 40 employees with hourly schedules in various departments. Something I need to do is coorellate that with some other data. The other data I have can go on a line chart with the obvious data point being 'start of the hour' for each event. So I have that already setup but I was asked to overlay essentially a count by department in each of those hours to show how many people WERE working in each department when the event happened. My event data is clean and has been used as it is for months hourly, but they want to see the schedule data now overlaid on it, by day of week (Because each day is different for some people but not for others. So I have it by hour and day of week in events, but can't figure out (likely tired), how to get this data into a place I can do that. I was debating making a chart of every hour of every day of every day of the week so that I can coorelate the data, knowing that my source will change soon I based the chart on the new source format so it should be pretty much a drop in replacement.

Here's an example of scheduling data:

Agent	Role	Monday Start	Monday End	Tuesday Start	Tuesday End	Wednesday Start	Wednesday End	Thursday Start	Thursday End	Friday Start	Friday End
Adam	cc	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00
George	cc	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00
Fred	cc	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00
Martha S.	cc			1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00
Martha Y.	cc			1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00
Melissa	cc			1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00
Jacob	cc	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00
Sven W.	cc	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00
John	cc	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00
Jessica	cc	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00
Jordan W	cc	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00
Jordan H	cc	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00
Megan	me	1899-12-30 11:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 18:00:00	1899-12-30 09:00:00	1899-12-30 18:00:00	1899-12-30 09:00:00	1899-12-30 18:00:00	1899-12-30 11:00:00	1899-12-30 20:00:00
Marcel W	me	1899-12-30 12:00:00	1899-12-30 21:00:00	1899-12-30 12:00:00	1899-12-30 21:00:00	1899-12-30 12:00:00	1899-12-30 21:00:00	1899-12-30 12:00:00	1899-12-30 21:00:00	1899-12-30 12:00:00	1899-12-30 21:00:00
Kelly	me	1899-12-30 11:00:00	1899-12-30 20:00:00	1899-12-30 11:00:00	1899-12-30 20:00:00	1899-12-30 11:00:00	1899-12-30 20:00:00	1899-12-30 11:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 18:00:00
Kristen	me			1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00	1899-12-30 08:00:00	1899-12-30 19:00:00
Kirsten	me	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00
Sue	cc			1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 10:00:00	1899-12-30 21:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00	1899-12-30 09:00:00	1899-12-30 20:00:00
Sidney	cc					1899-12-30 08:00:00	1899-12-30 21:00:00	1899-12-30 08:00:00	1899-12-30 21:00:00	1899-12-30 08:00:00	1899-12-30 21:00:00
Donatello	cc	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00	1899-12-30 08:00:00	1899-12-30 17:00:00

As you can see it converts the time format to datetime but realistically I only see hh:mm:ss (We do all times in EST to avoid confusion).

Is the path I should follow starting a large scale by day by hour style chart? 

[Anonymous · Accepted solution]

Hi @tagban ,
Depending on the information you provided, you can refer to the following steps:

1.Unpivot Columns.

2.Add two columns.

start time = 
CALCULATE (
    MIN ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)

end time = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Agent] = EARLIER ( 'Table'[Agent] ) )
)

3. Add new measure.

total = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Agent] ),
    FILTER (
        'Table',
        SELECTEDVALUE ( 'Table (2)'[Column1] ) >= 'Table'[start time]
            && SELECTEDVALUE ( 'Table (2)'[Column1] ) <= 'Table'[end time]
    )
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tagban]

Okay so this worked for the first part of my question. But I guess I need to make another calculated column to show the time selected to see if it actually falls within the timeframe selected. And then the same table of time with each hour linked to another report that shows it all. Otherwise i'm back to my create a seperate row for every hour of every day the people are working and link the two on "Value". Right?

[tagban]

I have to admit I did it wrong the first time i unpivoted selected rather than other and spent almost an hour trying to figure out why I'd do that.. but now it makes perfect sense.

Any advice on relationshipping this to another table with the names in it? Just wanting to show How many events each person has per hour and what their schedule was. 

[tagban]

My thoughts are so far to take both events table and show Day of week and Start of hour, then merge those two together, And at the same time, show a literal chart of each employee by day So like
Monday 11:00:00 AM, Monday 12:00:00 PM, etc with a 1 or 0 in it whether they work or not, I was just curious if there was a better method before I go down that rabbit hole.