Hi All,
Here is my scenario. I have a list of employees and the time ranges of when they have worked on different appointments. This data comes in every day and I would consolidate files for each day using power query. The sample data is as follows
I'm trying to calculate how many hours an employee worked on a particular days excluding the overlap times.
So for example employee ABC has three entries and all of them have some overlap. And same is the case with employee DEF. I'm trying to find a solution where I can define New Start Time where the new start time gets rid of the overlap problem.
For example
For employee ABC for row 4 New start time is End time for ABC in row 1 and same way new start time in row 5 is same as end time in row 4. I follow the same logic for DEF.
To calculate working hours I simply check with if condition whether start time > End Time. And if start time is greater than end time I just make it 0. For example second row for DEF.
How can I implement this in Power BI by either creating a new column as in my example above or may be using a measure in DAX.
Thanks for your help in advance!
PS: I posted this in excel forum also as for now an excel solution is good enough but I want to implement this in Power BI in few months so it would be good to know how to do this in Power BI.
Here is my scenario. I have a list of employees and the time ranges of when they have worked on different appointments. This data comes in every day and I would consolidate files for each day using power query. The sample data is as follows
| Employee | Scheduled_Start | Scheduled_End |
| ABC | 4/14/2020 9:00 | 4/14/2020 16:00 |
| DEF | 4/14/2020 9:00 | 4/14/2020 16:00 |
| DEF | 4/14/2020 11:00 | 4/14/2020 14:00 |
| ABC | 4/14/2020 15:00 | 4/14/2020 17:00 |
| ABC | 4/14/2020 16:00 | 4/14/2020 18:30 |
I'm trying to calculate how many hours an employee worked on a particular days excluding the overlap times.
So for example employee ABC has three entries and all of them have some overlap. And same is the case with employee DEF. I'm trying to find a solution where I can define New Start Time where the new start time gets rid of the overlap problem.
For example
| Employee | Scheduled_Start | Scheduled_End | New Start Time | New End Time | Hours Worked |
| ABC | 4/14/2020 9:00 | 4/14/2020 16:00 | 4/14/2020 9:00 | 4/14/2020 16:00 | 7 |
| DEF | 4/14/2020 9:00 | 4/14/2020 16:00 | 4/14/2020 9:00 | 4/14/2020 16:00 | 7 |
| DEF | 4/14/2020 11:00 | 4/14/2020 14:00 | 4/14/2020 16:00 | 4/14/2020 14:00 | 0 |
| ABC | 4/14/2020 15:00 | 4/14/2020 17:00 | 4/14/2020 16:00 | 4/14/2020 17:00 | 1 |
| ABC | 4/14/2020 16:00 | 4/14/2020 18:30 | 4/14/2020 17:00 | 4/14/2020 18:30 | 1.5 |
For employee ABC for row 4 New start time is End time for ABC in row 1 and same way new start time in row 5 is same as end time in row 4. I follow the same logic for DEF.
To calculate working hours I simply check with if condition whether start time > End Time. And if start time is greater than end time I just make it 0. For example second row for DEF.
How can I implement this in Power BI by either creating a new column as in my example above or may be using a measure in DAX.
Thanks for your help in advance!
PS: I posted this in excel forum also as for now an excel solution is good enough but I want to implement this in Power BI in few months so it would be good to know how to do this in Power BI.
