Proper duration counting

[MKPartner]

I have a case to calculate people work duration but within first activity, operator AA did another two activites so duration of first activity is smaller than 5.5h. Any idea how to calculate with DAX ? 

name	start_dttm	end_dttm
AA - first activity	28 Aug 25 06:09:36	28/08/2025 11:47
AA - second activity	28 Aug 25 07:22:26	28/08/2025 08:59
AA - third activity	28 Aug 25 09:08:30	28/08/2025 09:33
AA - fourth activity	28 Aug 25 12:19:43	28/08/2025 13:44

[v-dineshya]

Hi @MKPartner ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @bhanu_gautam , @Ahmedx , @Shahid12523 , @danextian  Thank you for your prompt responses.

Hi @MKPartner , Could you please try the proposed solutions shared by @bhanu_gautam , @Ahmedx , @Shahid12523  and @danextian  . Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @MKPartner ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[danextian]

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

[Shahid12523]

Solution: merge intervals first, then sum.

In DAX, you can build a measure that groups by operator, merges overlapping start/end times, then sums:

First activity (06:09–11:47) → 5h 38m (second & third inside, so ignored)

Fourth activity (12:19–13:44) → 1h 25m

Net = ~7h 03m

⚠️ DAX is tricky for merging intervals; Power Query (M) is simpler and recommended.

[Ahmedx]

pls try 

[bhanu_gautam]

@MKPartner 

 try using

AdjustedFirstActivityDuration =
VAR FirstActivity =
FILTER(Activities, Activities[Name] = "AA - first activity")

VAR FirstActivityStart =
MINX(FirstActivity, Activities[Start_DTTM])

VAR FirstActivityEnd =
MAXX(FirstActivity, Activities[End_DTTM])

VAR OverlappingActivities =
FILTER(
Activities,
Activities[Name] <> "AA - first activity" &&
Activities[Start_DTTM] < FirstActivityEnd &&
Activities[End_DTTM] > FirstActivityStart
)

VAR TotalOverlap =
SUMX(
OverlappingActivities,
MAX(0, DATEDIFF(
MAX(FirstActivityStart, Activities[Start_DTTM]),
MIN(FirstActivityEnd, Activities[End_DTTM]),
MINUTE
))
)

VAR TotalFirstActivityDuration =
DATEDIFF(FirstActivityStart, FirstActivityEnd, MINUTE)

RETURN
TotalFirstActivityDuration - TotalOverlap

[FarhanJeelani]

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.