Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 loggi...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks both of you! Got me started