Time spent in a session doing various activities

[Zosy]

Hi,

I am trying to calculate 2 separate things:
1. how much time in average does a customer spend doing certain activities
2. what proportion of a session was spent doing an activity


Desired results:

1. For activity A in average the user spent 49 seconds this year doing it
2. Activity B in Session 8 was 0.81% out of the entire session 8.



Data: There are 2 separate tables and no Date table is used.

Activity Table

StartTime	EndTime	ActivityName	SessionId
15/01/2025 15:30	15/01/2025 15:31	A	1
09/01/2025 15:29	09/01/2025 15:30	A	2
03/01/2025 14:23	03/01/2025 14:25	A	3
27/01/2025 09:16	27/01/2025 09:17	A	4
23/01/2025 10:15	23/01/2025 10:15	A	5
15/01/2025 10:13	15/01/2025 10:14	A	6
13/01/2025 12:49	13/01/2025 12:50	A	7
16/01/2025 11:02	16/01/2025 11:03	A	8
16/01/2025 11:06	16/01/2025 11:07	B	8

Session Table

StartTime	EndTime	SessionId
15/01/2025 15:02	15/01/2025 15:57	1
09/01/2025 15:01	09/01/2025 15:37	2
03/01/2025 11:10	03/01/2025 15:31	3
27/01/2025 09:14	27/01/2025 09:17	4
23/01/2025 10:15	23/01/2025 10:16	5
15/01/2025 10:13	15/01/2025 10:14	6
13/01/2025 11:07	13/01/2025 13:22	7
16/01/2025 10:32	16/01/2025 12:27	8

Any help is greatly appreciated.

[DataNinja777 · Accepted solution]

Hi @Zosy ,

You can calculate the average time a customer spends on an activity by computing the duration for each activity, filtering for the current year, and then taking the average. The following DAX measure achieves this:

Avg Activity Duration (Seconds) =
VAR CurrentYear = YEAR(TODAY())
VAR FilteredActivities =
    FILTER('Activity Table', YEAR('Activity Table'[StartTime]) = 2025)
VAR ActivityDurations =
    ADDCOLUMNS(
        FilteredActivities,
        "Duration", DATEDIFF([StartTime], [EndTime], SECOND)
    )
RETURN
    AVERAGEX(ActivityDurations, [Duration])

To determine what proportion of a session was spent on an activity, you need to calculate the total session duration and compare it to the total time spent on the selected activity within that session. The following measure accomplishes this:

Activity Proportion in Session (%) =
VAR SessionDuration =
    CALCULATE(
        DATEDIFF(
            MIN('Session Table'[StartTime]),
            MAX('Session Table'[EndTime]),
            SECOND
        ),
        'Session Table'[SessionId] = SELECTEDVALUE('Activity Table'[SessionId])
    )
VAR ActivityDuration =
    SUMX(
        FILTER(
            'Activity Table',
            'Activity Table'[SessionId] = SELECTEDVALUE('Session Table'[SessionId])
        ),
        DATEDIFF([StartTime], [EndTime], SECOND)
    )
RETURN
    DIVIDE(ActivityDuration, SessionDuration, 0) * 100

Using these formulas, you can determine that for activity A, the average time spent this year is approximately 49 seconds, while activity B in session 8 accounts for about 0.81% of the total session duration. These measures ensure accurate calculations without the need for a Date Table, as all calculations rely directly on the provided timestamps.

Best regards,

[Zosy]

Thank you for the clarifications! It worked 🙂