Forum Discussion

mathpedrosa's avatar
mathpedrosa
Regular Visitor
1 year ago
Solved

Time difference between timestamps from different rows

Hi all! 

 

I need to do a dashboard where it shows worked hours for every employee, divided by each day. But I'm struggling with data treatment... could you please help me figure it out?

 

I have this structure:

 

EmployeeIDSTATUSTIMESTAMP
1Start01/01/2025 08:00
2Start01/01/2025 09:00
3Start01/01/2025 09:30
2End01/01/2025 16:30
1End01/01/2025 17:30
3End01/01/2025 18:00
1Start01/02/2025 09:30
2Start01/02/2025 14:30
1End01/02/2025 17:30
3Start01/02/2025 18:30
2End01/02/2025 22:30
3Start01/03/2025 01:30

 

Thank you!

 

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    If you know that the start and end will always be on the same day then its reasonably straightforward. If people can start on 1 day and finish on a different day then you would ideally need a unique shift identifier to be able to match start and end times to the same shift.

    If shifts do start and end on the same day, or you have a unique identifier, then you can use group by in Power Query to achieve the result. If you are using the dates, as opposed to a unique identifier, then create a new column called [Start Date] by extracting just the date from the [TIMESTAMP] column.

    Group the table by employee number and start date, and add 2 aggregations. Add a Min of [TIMESTAMP] and call it Start, and a max of [TIMESTAMP] called End.

    Add a new column which is [End] - [Start] called Worked Duration.

    Finally, transform the [Worked Duration] to extract the total hours.

    See the M code below, also the attached PBIX.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqAdIGhvpAZGRgZKpgYGFlYKAUqxOtZIRDgSVMgTFOBcYIE1zzUlClDc1g0oZYpc1h0sZYpeHOQ3e/ETbbsSgwNMFuvxE2+7Hpt8DuPai0kREu/cZQBxqCFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, STATUS = _t, TIMESTAMP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"STATUS", type text}, {"TIMESTAMP", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([TIMESTAMP]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"EmployeeID", "Date"}, {{"Start", each List.Min([TIMESTAMP]), type nullable datetime}, {"End", each List.Max([TIMESTAMP]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Worked Duration", each [End] - [Start]),
    #"Calculated Total Hours" = Table.TransformColumns(#"Added Custom",{{"Worked Duration", Duration.TotalHours, type number}})
in
    #"Calculated Total Hours"

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    If you know that the start and end will always be on the same day then its reasonably straightforward. If people can start on 1 day and finish on a different day then you would ideally need a unique shift identifier to be able to match start and end times to the same shift.

    If shifts do start and end on the same day, or you have a unique identifier, then you can use group by in Power Query to achieve the result. If you are using the dates, as opposed to a unique identifier, then create a new column called [Start Date] by extracting just the date from the [TIMESTAMP] column.

    Group the table by employee number and start date, and add 2 aggregations. Add a Min of [TIMESTAMP] and call it Start, and a max of [TIMESTAMP] called End.

    Add a new column which is [End] - [Start] called Worked Duration.

    Finally, transform the [Worked Duration] to extract the total hours.

    See the M code below, also the attached PBIX.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqAdIGhvpAZGRgZKpgYGFlYKAUqxOtZIRDgSVMgTFOBcYIE1zzUlClDc1g0oZYpc1h0sZYpeHOQ3e/ETbbsSgwNMFuvxE2+7Hpt8DuPai0kREu/cZQBxqCFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, STATUS = _t, TIMESTAMP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"STATUS", type text}, {"TIMESTAMP", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([TIMESTAMP]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"EmployeeID", "Date"}, {{"Start", each List.Min([TIMESTAMP]), type nullable datetime}, {"End", each List.Max([TIMESTAMP]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Worked Duration", each [End] - [Start]),
    #"Calculated Total Hours" = Table.TransformColumns(#"Added Custom",{{"Worked Duration", Duration.TotalHours, type number}})
in
    #"Calculated Total Hours"
  • Elena_Kalina's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

    Hi mathpedrosa 

    Based on your timestamp data, I'll help you build a dashboard showing daily worked hours per employee. Here's the step-by-step solution:

    Step 1: Data Preparation in Power Query

    First, we need to transform your raw data into a usable format:

    1. Add a custom column to extract the date (without time):

      = DateTime.Date([TIMESTAMP])

    2. Sort your data by EmployeeID, then TIMESTAMP

    Step 2: Calculate Work Durations

    Create a calculated table to pair Start/End times:

    WorkedHours = 
VAR StartTimes = 
    FILTER(
        'YourTable',
        [STATUS] = "Start"
    )
VAR EndTimes = 
    FILTER(
        'YourTable',
        [STATUS] = "End"
    )
RETURN
GENERATE(
    StartTimes,
    VAR CurrentEmployee = StartTimes[EmployeeID]
    VAR CurrentDate = DateTime.Date(StartTimes[TIMESTAMP])
    VAR CurrentStart = StartTimes[TIMESTAMP]
    VAR MatchingEnd = 
        TOPN(
            1,
            FILTER(
                EndTimes,
                EndTimes[EmployeeID] = CurrentEmployee &&
                DateTime.Date(EndTimes[TIMESTAMP]) = CurrentDate &&
                EndTimes[TIMESTAMP] > CurrentStart
            ),
            EndTimes[TIMESTAMP], ASC
        )
    RETURN
    ROW(
        "HoursWorked", DATEDIFF(CurrentStart, SELECTCOLUMNS(MatchingEnd, "EndTime", [TIMESTAMP]), MINUTE)/60,
        "Date", CurrentDate,
        "EmployeeID", CurrentEmployee,
        "StartTime", CurrentStart,
        "EndTime", SELECTCOLUMNS(MatchingEnd, "EndTime", [TIMESTAMP])
    )
)

    Step 3: Create Dashboard Visuals

    Visual 1: Daily Hours by Employee (Matrix)

    • Rows: EmployeeID

    • Columns: Date (from the calculated column)

    • Values: SUM of HoursWorked

    • Format as decimal (e.g., 8.5 hours)

    Visual 2: Employee Daily Hours Bar Chart

    • X-axis: Date

    • Y-axis: HoursWorked

    • Legend: EmployeeID

    • Set to stacked or clustered view

    Visual 3: Employee Summary Card

    • Total hours per employee (measure)

    • Average hours per day

    • Number of work days

    Step 4: Add Useful Measures

    Total Hours = SUM(WorkedHours[HoursWorked])

Daily Average = 
AVERAGEX(
    SUMMARIZE(
        WorkedHours,
        WorkedHours[EmployeeID],
        WorkedHours[Date],
        "DailyHours", SUM(WorkedHours[HoursWorked])
    ),
    [DailyHours]
)

    Step 5: Final Touches

    1. Add slicers for:

      • Date range

      • Employee selection

    2. Conditional formatting to highlight:

      • Short/long work days

      • Overtime (e.g., >8 hours)

    3. Tooltips showing:

      • Exact start/end times

      • Break durations (if applicable)

    Handling Data Gaps

    For incomplete records (missing Start/End), add this measure:

    Valid Records = 
COUNTROWS(
    FILTER(
        WorkedHours,
        NOT(ISBLANK(WorkedHours[HoursWorked]))
    )