- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Working hours considering overlap times

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

dnavia

Frequent Visitor

Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-13-2018
04:33 AM

Good morning everyone!

I was wondering if I could get some help with the following problem. For a specific task inside our site, I am getting the following information directly from the WMS:

- USER ID

- Task ID

- Start Time

- End Time

- Quantity of SKU's processed

From that information, I am trying to get the working hours and productivity for each user. The problem is that due to the client needs, some colaborators have to perform tasks in bewteen. For example, let's consider the following:

- I started TASK1 at 10:30 with 10 SKU's

- I started TASK2 at 10:43 with 2 SKU's

- I finished TASK2 at 10:55

- I finished TASK1 at 11:15

I would like to consider:

- If there is an overlap, consider the earliest start time (10:30)

- If there is an overlap, consider the latest end time (11:15)

- Therefore the working hours would be 45 minutes

- Consider the total SKU's processed (12 lines)

- Productivity: 12/45 = 16 sku's per hour

I also have to consider scenarios where TASK2 would start after TASK1 and end also after TASK1 aswell:

- TASK1 START TIME: 10 AM

- TASK 2 START TIME: 10:30 AM

- TASK 1 END TIME 10:40 AM

- TASK 2 END TIME: 10:45

Another possible solution that I was thinking about was consider the total SKU's processed by one user on a shift, and afterwards, consider the total working hours and then substract the overlaping times. I would really appreciate any help with this scenarios.

Best regards, Diego

Report Inappropriate Content

Message 1 of 8

7 REPLIES 7

AlB

Super User

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-13-2018
05:38 AM

Hi @dnavia

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.

dnavia

Frequent Visitor

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
06:40 AM

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

Super User

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
07:17 AM

AlB

Super User

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-14-2018
07:54 AM

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)?

dnavia

Frequent Visitor

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2018
08:46 AM

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 hour

On 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

Super User

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-17-2018
12:42 PM

dnavia

Frequent Visitor

Re: Working hours considering overlap times

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2018
07:19 AM

I already found a way to consider only times between working hours (09:00 - 12:00 and 13:15 - 17:15) but I am missing the part on how to consider time without the overlaps.

Once again thank you for your help!