Forum Discussion
Working hours considering overlap times
Community ChampionHi Anonymous
I'll propose a pretty simple solution to start with. It will probably need refinement depending on how you want to deal with data for several days. This would probably work at the day level:
1. Set [USER ID] on the rows of a matrix visual. Date (day level) on the columns
2. Set a measure like this in values of the matrix:
Productivity =
DIVIDE (
SUM ( Table1[NumSKUs] );
( MAX ( Table1[End Time] ) - MIN ( Table[Start Time] ) ) * 24
)This should give you productivity in SKUs per hour. It assumes the time columns are in Date/Time format.
Hey AIB,
First of all thank you for taking the time for reading and helping me with this!
Regarding the data, for example, my shift is from 8:00 AM until 17:30 PM, durning that time I am supposed to be working on a specific task, but due to activities related to the work, helping a coworker, breaks, being on the phone, going for lunch, etc. I don't spend the whole shift at those specific tasks that I'm suppose to do.
Therefore I would like to calculate the amount of time that I am actually doing that specific task, that is why I am trying to calculate the duration of each activity (End - Start time) and then sum all the durations from one shift. And based on that time and considering the amount of SKU's processed, determine:
- The "real" working hours
- The productivity (SKU's / Working hours)
I hope that clarifies the problem that I'm facing.
Once again thank you for your help!
- AlB
Hi Anonymous
Can you share the pbix? It'd be easier to help you with that
AlBPlease find the sample .pbix. I am facing same issue. https://drive.google.com/file/d/19XcxqyJcBxQy1mZSop4_cF1iEEGm61pJ/view?usp=sharing
- AlB
Anonymous
OK, so the real working hours would be the sum of (End_Time - Start_Time) for all activities per user and day, correct?
do you have data on several days? (I guess so)
Can you show the structure of the tables in your model (in yo do not share the pbix)?
Sorry for the delay, I was out of office on friday.
Responding to your questions:
OK, so the real working hours would be the sum of (End_Time - Start_Time) for all activities per user and day, correct?
- Correct, but the problem that I have is that if one person performs 2 or more tasks durning the same (or part of the) time, I should only consider the amount of time once, but sum the total SKU's processed.
do you have data on several days? (I guess so)
- Yes i do, usually I work this data on Excel on periods of 5 days (weekly data) or 20-22 days (monthly data)
Can you show the structure of the tables in your model (in yo do not share the pbix)?
I tried to make something on power BI but it ended being pretty messy, here is part of the raw data extracted from the data base:
TASK_ID START_DATE END_DATE SKU UNITS USER TASK1 07-12-2018 14:17 07-12-2018 14:58 2 3 USER1 TASK2 07-12-2018 14:24 07-12-2018 14:41 2 2 USER1 TASK3 07-12-2018 14:25 07-12-2018 14:43 1 8 USER1 TASK4 07-12-2018 14:25 07-12-2018 14:35 1 1 USER2 TASK5 07-12-2018 14:28 07-12-2018 14:35 1 1 USER3 TASK6 07-12-2018 14:30 07-12-2018 14:32 2 2 USER4 TASK7 07-12-2018 14:30 07-12-2018 14:49 2 2 USER5 TASK8 07-12-2018 14:40 07-12-2018 14:52 2 5 USER2 TASK9 07-12-2018 14:45 07-12-2018 14:59 2 2 USER1 TASK10 07-12-2018 14:49 07-12-2018 15:01 1 1 USER3 TASK11 07-12-2018 14:49 07-12-2018 15:35 1 1 USER5 For example, for task 1, 2, 3 and 9 they were done by the same "USER1",
therefore i should add the total amount of time worked (end - start date) but without the overlaps. which would be 14:59 - 14:17 = 42 minutes.
Besides, in total he worked over 7 SKU's, so the productivity would be 7 / 42 (skus/min) -> 10 sku's per hourOn the other side, for USER2, he did 2 tasks without any overlap, so the total time would be the sum of the working hours.
And therefore the productivity would be: (1+2) /( (14:52 - 14:40) + (14:35 - 14:25))
Once again thank you for your help!
- AlB
Anonymous
Ok, I think I get it. Could you post some more data including more than one day?