topic Employee Utilization Per Hour Report in Custom Visuals Development Discussion

Employee Utilization Per Hour Report

kdubey — Tue, 02 Jul 2024 05:18:03 GMT

Hello all,

I have to build report to show employee utilization per hour.

Example -
User XY is performing task as follows -

Task StartTime EndTime
AB 8:00 am 9:15 am
CD 9:20 am 10:00 am
EF 10:20 am 11:15 am

Resulting report in PowerBI -

Employee	Time bracket	Occupied (in mins)	Occupied (in hours)
XY	8:00 AM - 9:00 AM	60	1 - (60/60)
	9:00 AM - 10:00 AM	55	0.916 - (55/60)
	10:00 AM - 11:00 AM	40	0.66 - (40/60)
	11:00 AM - 12:00 PM	15	0.25 - (15/60)

Here are my tables:

Task	employee Activity	Current employee	Start date time	End date time
227	DRY LIFT	p7k	April 11, 2024 at 7:09:38 PM EDT	April 11, 2024 at 7:10:47 PM EDT
225	DRY LIFT	l	April 11, 2024 at 7:10:55 PM EDT	April 11, 2024 at 7:12:43 PM EDT
226	DRY LIFT	p7k	April 11, 2024 at 7:10:53 PM EDT	April 11, 2024 at 7:13:18 PM EDT
224	DRY PULL	m3s	April 11, 2024 at 7:36:49 PM EDT	April 11, 2024 at 7:57:08 PM EDT
221	DRY PULL	p7e	April 11, 2024 at 7:34:41 PM EDT	April 11, 2024 at 7:42:11 PM EDT
212	DRY PULL	p	April 11, 2024 at 7:10:32 PM EDT	April 11, 2024 at 7:14:27 PM EDT
22	DRY PULL	o9y	April 11, 2024 at 7:34:02 PM EDT	April 11, 2024 at 7:49:54 PM EDT

I am new to PowerBI, Please can someone guide how should I proceed to make this report like step by step?

Re: Employee Utilization Per Hour Report

lbendlin — Tue, 02 Jul 2024 23:47:44 GMT

First step: bring your date time values into a usable format.

Perhaps surprisingly, "April 11, 2024 at 7:09:38 PM EDT" is really hard for Power Query to understand.

Much easier with this format (called ISO-8601): "2024-04-11T19:09:38 -04:00"

Next step is pro provide meaningful sample data. Yours all falls into the same "time bracket" hour slot. That's not sufficient to provide you with a good solution proposal.

Re: Employee Utilization Per Hour Report

kdubey — Wed, 03 Jul 2024 04:33:51 GMT

Thank you for your response. Sure, I will make suggested changes in time column.

Is this sample format good ?

Original Employee	Task	Start Date Time	End Date Time	Activity
k1q	W284863	June 24, 2024 at 12:19:05 AM EDT	June 24, 2024 at 1:05:00 AM EDT	Dry Selection
s2f	WR024827	June 24, 2024 at 12:45:31 AM EDT	June 24, 2024 at 1:56:00 AM EDT	Dry Selection
r6y	OBP00999479	June 24, 2024 at 12:55:12 AM EDT	June 24, 2024 at 1:06:00 AM EDT	STAGE SELECTION PALLET LIFT
k2r	WR85144	June 24, 2024 at 1:00:02 AM EDT	June 24, 2024 at 1:05:00 AM EDT	Dry Selection
o8j	IBPW3871622	June 24, 2024 at 1:41:19 AM EDT	June 24, 2024 at 1:41:19 AM EDT	DYN PUTAWAY
m7y	IBP03871734	June 24, 2024 at 2:09:43 AM EDT	June 24, 2024 at 2:30:00 AM EDT	SLOTTING PUT
m5w	WR085293	June 24, 2024 at 2:10:36 AM EDT	June 24, 2024 at 2:48:00 AM EDT	Dry Selection
q35	IBPW3872188	June 24, 2024 at 6:31:04 AM EDT	June 24, 2024 at 6:35:05 AM EDT	DRY REPLEN PUT ACTIVE
1o4v	IBPW3872202	June 24, 2024 at 7:46:59 AM EDT	June 24, 2024 at 7:46:59 AM EDT	DYN PUTAWAY
pt6w	IBPWP3872205	June 24, 2024 at 7:48:19 AM EDT	June 24, 2024 at 7:48:19 AM EDT	DYN PUTAWAY
po4v	IBP872206	June 24, 2024 at 7:49:07 AM EDT	June 24, 2024 at 7:49:07 AM EDT	DYN PUTAWAY
po4v	IBPWP872229	June 24, 2024 at 8:00:58 AM EDT	June 24, 2024 at 8:00:58 AM EDT	DYN PUTAWAY
po4v	IBPWP872231	June 24, 2024 at 8:02:53 AM EDT	June 24, 2024 at 8:02:53 AM EDT	DYN PUTAWAY
po4v	IBPW872234	June 24, 2024 at 8:04:12 AM EDT	June 24, 2024 at 8:04:12 AM EDT	DYN PUTAWAY
po4v	IBPW872240	June 24, 2024 at 8:06:31 AM EDT	June 24, 2024 at 8:06:31 AM EDT	DYN PUTAWAY
pt6w	IBPW3872241	June 24, 2024 at 8:07:24 AM EDT	June 24, 2024 at 8:07:24 AM EDT	DYN PUTAWAY

Re: Employee Utilization Per Hour Report

lbendlin — Thu, 04 Jul 2024 20:34:25 GMT

Not yet - Please read up on ISO-8601 format.

Here's a graphical representation. What is the business purpose of bucketing this by hours?

Note: Most of the events here have zero length so do not get rendered.