Forum Discussion
Sum total time between timestamps from logging table
Hello
I have two tables below, one with employees and one table with transactions where they start and stop their clock. I need to sum the total of hours between the start and stop for each logging. I'm stuck and would appreciate your help
There is no ID to the transaction, so I need to calculate the time between the first <Start> and the first <Stop>, then move to the second <start> against the second <stop>, and so on. There can be thousands of transactions in the real table
| Employees | ||
| ID | Employee Name | |
| 1 | Mike | |
| 2 | Steve | |
| Timelog | ||
| EmployeeID | Activity | Timestamp |
| 1 | Start | 2018-11-20 10:00 |
| 1 | Stop | 2018-11-20 11:00 |
| 1 | Start | 2018-11-20 14:00 |
| 1 | Stop | 2018-11-20 15:00 |
| 2 | Start | 2018-11-20 15:00 |
| 2 | Stop | 2018-11-20 16:00 |
| End result | ||
| Employee | Total time (hours) | |
| Mike | 2 | |
| Steve | 1 |
Hi Anonymous,
The solution of PattemManohar should be useful.
By my tests, you could create a calculated column with the formula below then you could get your desired output.
Hour = VAR a = CALCULATE ( MAX ( 'Timelog'[Timestamp] ), FILTER ( ALL ( 'Timelog' ), 'Timelog'[Timestamp] < EARLIER ( 'Timelog'[Timestamp] ) && 'Timelog'[EmployeeID] = EARLIER ( 'Timelog'[EmployeeID] ) && 'Timelog'[Activity] <> EARLIER ( 'Timelog'[Activity] ) && 'Timelog'[Activity] = "Start" ) ) RETURN DATEDIFF ( a, 'Timelog'[Timestamp], HOUR )Here is the output.
Best Regards,
Cherry
3 Replies
- PattemManohar
Community Champion
Anonymous I've recently posted a solution to same scenario, please have a look if that helps and post any questions...
https://community.powerbi.com/t5/Desktop/calculating-working-time-in-office/m-p/567548#M267842
v-piga-msftResident Rockstar
Hi Anonymous,
The solution of PattemManohar should be useful.
By my tests, you could create a calculated column with the formula below then you could get your desired output.
Hour = VAR a = CALCULATE ( MAX ( 'Timelog'[Timestamp] ), FILTER ( ALL ( 'Timelog' ), 'Timelog'[Timestamp] < EARLIER ( 'Timelog'[Timestamp] ) && 'Timelog'[EmployeeID] = EARLIER ( 'Timelog'[EmployeeID] ) && 'Timelog'[Activity] <> EARLIER ( 'Timelog'[Activity] ) && 'Timelog'[Activity] = "Start" ) ) RETURN DATEDIFF ( a, 'Timelog'[Timestamp], HOUR )Here is the output.
Best Regards,
Cherry
