Sum of TotalHours between reoccuring activity name in DAX

[BurgerToPro]

Thanks in advance for helping a new member out!

I am working in the transport sector and would like to create an overview that shows how many hours a driver has spent in a country during a certain week for each time he has been in a country in chronological order. So far, I only achieved an overview of the total hours a driver has spent in each country and not every time it has been in a country.

The result would look something like this and (if possible) should be solved in DAX:

Slicer: Driver ID = 301
Slicer: Weeknr = 13

CountryID	Total Hours
31	10
32	5
31	3
32	8
33	12
32	6

My data
I have 1 big table that consists of the following:

ActivityStartDateTime	ActivityID	DriverID	TotalHours	ActivityName	Many more columns....
18-05-21 15:09:00	100123	316	3,6	Driving
18-05-21 15:39:00	10124	316	0,5	Pause
18-05-21 16:39:00	10125	316	1	Driving
18-05-21 16:54:00	10126	316	0,25	Border
18-05-21 16:59:00	10127	316	1,05	Driving
18-05-21 19:09:00	10128	316	2,12	Border
+1.499.999 rows

We can assume that ActivityStartDate is in chronological order for all activities, this is not the case for ActivityID.
This table has 100+ different drivers and in total has 1.5 million rows.

The problem

When I select a week and a driver with a slicer, I need an overview of his total hours. The first activity for that driver in that week decides the start country. To select this activity I thought about min(ActivityStartDateTime).

From this activity, I would like to Sum(TotalHours) until I reached the first ActivityName = "Border".

For the second country I have to Sum(TotalHours) from the first ActivityName = "Border" until the second ActivityName = "Border".

This has to continue until I reached the Max(ActivityStarteDateTime) in that week.
It is possible that a driver will cross 8 borders in one week. Then I would need a result that lists 9 different countries. (Start country + 8 new ones)

P.S.

This is my first post, so let me know if I need to change/add anything. I have spent a lot of hours trying to solve this problem using the shared knowledge in the community. Unfortunately, I did not achieve the desired result.
Good luck!

[v-easonf-msft · Accepted solution]

Hi, @BurgerToPro 

Is there a field ‘CountryID’ in your data table? Does it allow multiple duplicate values in your result?

If so, add the following calculated column for grouping and sorting, than apply it to your table:

Sort_Country =
CALCULATE (
    COUNT ( 'Table'[ActivityName] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[DriverID] ),
        'Table'[ActivityStartDateTime] < EARLIER ( 'Table'[ActivityStartDateTime] )
            && 'Table'[ActivityName] = "Border"
    )
) + 1

Please share more details if I misunderstood.

Best Regards,
Community Support Team _ Eason