Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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]
)
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
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
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
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 |
Hi @camargos88
This looks great! thanks so much for your help I think this will do exactly what I need!
Thanks
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
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |