Forum Discussion
Helper IMeasure to calculate time worked
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
9 Replies
- Greg_Deckler
Community Champion
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
camargos88Hi 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
mik618Thanks 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
jstormResolver III
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]
)
