cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArashZ
Helper I
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.

 

UserNumber of sessionsSession dateSession Duration
A128/07/2021 10 AM100
B329/07/2021 11 AM130
B329/07/2021 12 PM200
B330/07/2021 13 PM250
C412/08/2021 10 AM40
C412/08/2021 11 AM30
C412/08 2021 13 PM120
C413/08/2021 14 PM140

 

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

 

UserSum
A100
B330
B330
B250
C190
C190
C190
C140

 

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
VahidDM
Community Champion
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:

VahidDM_0-1632521540784.png

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

@ArashZ Try:

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_DecklerHi,

 

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

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.