Time spent in a session doing various activities

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,