Sum total time between timestamps from logging table

[Anonymous]

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

[v-piga-msft · Accepted solution]

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

[Anonymous]

Thanks both of you! Got me started

[PattemManohar]

@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