Measure to calculate time worked

[mik618]

Hi There, 

I'm looking to try and calculate a measure to show how mant hours & Minutes a staff member worked per day. 

Staff work on appointments that have a start date and time, and an end date and time, so I can calculate the total time per appointment.

the problem I'm having it that the appointments overlap, as staff can see up to 10 people at a time, so in one day the staffs table might look like this;

		start	end	total hours
staffMemberA	appointment1	1pm	2pm	1
staffMemberA	appointment2	3pm	5pm	2
staffMemberA	appointment3	4pm	6pm	2
staffMemberA	appointment4	7pm	8pm	1
			total hours worked	5

I can't figure out how to only add up hours and minutes the staff had appointments in total

any help would be greatly appreciated 

Thanks 

[jstorm]

You could use a CALCULATE function with MIN() and MAX() to determine start and end times for the employee's day then simply find the difference.

EX.

CALCULATE(
    MAX( [End] ) -

    MIN( [Start] ),

    [Filter1]

)

[mik618]

HI @jstorm 

Thanks, but the problem with that is that there are gaps in the day where the staff won't be wokring and I don't want to include this time

[camargos88]

Hi @mik618 ,

I've created this file: Download PBIX 

It creates the ranges for your appointments, so you can calculate without overlaps.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[mik618]

Thanks @camargos88 for this, 

The only problem (my fault for simplifying the example I gave!) is that appointments can be a matter of minutes, rather than hours, so really i need to count minutes worked, and later convert to total hours and minutes,

Thanks for your help

[camargos88]

Hi @mik618 ,

Can you provide some data as example ?

Ricardo

[mik618]

Thanks @camargos88 

for example a snip of the staff table might look like this;

Staff	Appointment	Start	End
Staff1	Appointment1	1.15pm	2.10pm
Staff1	Appointment2	3pm	5.15pm
Staff1	Appointment3	4pm	5.30pm
Staff1	Appointment4	7pm	8pm
Staff2	Appointment1	9.15am	4pm
Staff2	Appointment2	9am	2pm

[camargos88 · Accepted solution]

Hi @mik618 ,

I've updated the file: Download PBIX 

Ricardo

[mik618]

Hi @camargos88 

This looks great! thanks so much for your help I think this will do exactly what I need!

Thanks 

[Greg_Deckler]

I wonder if something like Open Tickets might help. Have to think it through though.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147