cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...