cancel
Showing results for
Did you mean:
Helper I

## Session duration per user per day

Hi,

I want to calculate the session duration per user per day. In the table below, we have different users with different sessions date.

 User Number of sessions Session date Session Duration A 1 28/07/2021 10 AM 100 B 3 29/07/2021 11 AM 130 B 3 29/07/2021 12 PM 200 B 3 30/07/2021 13 PM 250 C 4 12/08/2021 10 AM 40 C 4 12/08/2021 11 AM 30 C 4 12/08 2021 13 PM 120 C 4 13/08/2021 14 PM 140

I am looking for a calculated column to sum the sessions per user per day. The answer should look like this:

 User Sum A 100 B 330 B 330 B 250 C 190 C 190 C 190 C 140

You can see that for example, for user B, the first two rows are 330. It's because they happened on the same day.

Thanks.

4 REPLIES 4
Community Champion

Hi @ArashZ  Jan,

Try this code to add a new column:

``````Sum =
CALCULATE (
SUM ( 'Table'[Session Duration] ),
ALLEXCEPT ( 'Table', 'Table'[User], 'Table'[Session date] )
)``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

@ArashZ Try:

``````Column =
VAR __User = [User]
VAR __Date = [Session date]
RETURN
SUMX(FILTER('Table',[User]=__User && [Session date] = __Date),[Session Duration])``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helper I

The solution only calculates per user, not per day. Can you fix that?

Super User

@ArashZ Ah, I missed there were different times on same day. Try:

``````Column =
VAR __User = [User]
VAR __Date = TRUNC([Session date])
RETURN
SUMX(FILTER('Table',[User]=__User && TRUNC([Session date]) = __Date),[Session Duration])``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!