Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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✌️!!
@ArashZ Try:
Column =
VAR __User = [User]
VAR __Date = [Session date]
RETURN
SUMX(FILTER('Table',[User]=__User && [Session date] = __Date),[Session Duration])
@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])
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |