Session duration per user per day

[ArashZ]

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.

[VahidDM]

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.

Appreciate your Kudos✌️!!

[Greg_Deckler]

@ArashZ Try:

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

[ArashZ]

@Greg_DecklerHi,

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

[Greg_Deckler]

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