Proper duration counting

Hi MKPartner ,

28 Aug 2025 06:09:36 to 11:47
28 Aug 2025 12:19:43 to 13:44 Total duration = 5h38m + 1h25m = 7h03m.
Why If you simply sum each activity’s duration, the overlapping time (07:22–08:59 and 09:08–09:33 inside the first block) gets counted more than once. The correct approach is to merge overlapping intervals per person and then sum the lengths of the merged intervals.

 

My approach: merge intervals in Power Query, then a simple DAX measure on the merged table

Do the heavy lifting in Power Query: for each person, sort by start, merge any overlapping (or touching) intervals, and then compute duration per merged interval.

Create a final table (e.g., MergedActivities) with columns: name, Start_dttm, End_dttm, DurationMin (End_dttm - Start_dttm in minutes).

Then a simple measure: Total Duration Hours = SUMX( MergedActivities, DATEDIFF(MergedActivities[Start_dttm], MergedActivities[End_dttm], MINUTE) ) / 60
Concrete outline to implement in Power Query

Start with a table like: name | start_dttm | end_dttm AA - first activity | 2025-08-28 06:09:36 | 2025-08-28 11:47 AA - second activity| 2025-08-28 07:22:26 | 2025-08-28 08:59 AA - third activity | 2025-08-28 09:08:30 | 2025-08-28 09:33 AA - fourth activity| 2025-08-28 12:19:43 | 2025-08-28 13:44

 

In Power Query:

Add an Index column to preserve a deterministic order (optional but helpful).
Group by name and collect the rows for each person as a nested table.
For each person’s nested table, sort by start_dttm and merge overlaps:
Start with the first interval.
For each subsequent interval, if its start <= current end, extend the current end to max(current end, interval end).
Otherwise, start a new merged interval.

 

Expand the merged intervals back to rows: name, Start_dttm, End_dttm.
Add a column DurationMin = Duration.TotalMinutes(End_dttm - Start_dttm).
Load as a new table (MergedActivities).
Result for your sample (AA):

Merge1: Start 2025-08-28 06:09:36, End 2025-08-28 11:47
Merge2: Start 2025-08-28 12:19:43, End 2025-08-28 13:44
Durations: 5h38m + 1h25m = 7h03m_

 

Please mark this post as solution if it helps you. Appreciate Kudos.

hI MKPartner 

I'm not exactly clear what you want to achieve but if you're trying to get the total duration for all first activty rows, try this:

duration - first activity = 
VAR _duration =
    SUMX (
        FILTER (
            VALUES ( 'Table'[name] ),
            CONTAINSSTRING ( 'Table'[name], "first activity" )
        ),
        CALCULATE ( MAX ( 'Table'[end_dttm] ) - MAX ( 'Table'[start_dttm] ) )
    )
VAR _days =
    INT ( _duration )
VAR _time = _duration - _days
VAR _formattedDuration =
    IF ( _days > 0, FORMAT ( _days, "0." ) )
        & FORMAT ( _time, "HH:MM:SS" )
RETURN
    _formattedDuration