cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrejBI
Helper V
Helper V

How to calculate time duration of a session from a column?

Hi,

I have session events data in a table like this, how would you approach calculating time duration per session? 

AndrejBI_0-1617785297144.png

As you can see, a red and a blue circles are 2 different sessions the data is sorted descending. The logic behind the calculation should be something like Max timestamp value - min timestamp value segmented by Session ID. So for the red circle it would be 4:53

 

2 ACCEPTED SOLUTIONS
v-yuaj-msft
Community Support
Community Support

Hi @AndrejBI ,

 

Based on your description, you can create a measure as follows.

Here is my test table.

v-yuaj-msft_0-1617940532127.png

Test = 

var x1=MAXX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])

var x2=MINX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])

return

x1-x2
v-yuaj-msft_1-1617940556038.png

Result:

v-yuaj-msft_2-1617940605351.png

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

Here's a screenshot

Gabriel_Walkman_0-1618384446549.png

 

View solution in original post

6 REPLIES 6
v-yuaj-msft
Community Support
Community Support

Hi @AndrejBI ,

 

Based on your description, you can create a measure as follows.

Here is my test table.

v-yuaj-msft_0-1617940532127.png

Test = 

var x1=MAXX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])

var x2=MINX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])

return

x1-x2
v-yuaj-msft_1-1617940556038.png

Result:

v-yuaj-msft_2-1617940605351.png

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

Gabriel_Walkman
Resolver III
Resolver III

In power query, I would copy and rename the table (if you need the intermediate timestamps for something), rename timestamp to starttime, make a copy of it called endtime, then group by id and in the popup add min/max aggregation accordingly for start/endtime.

Edit: oh and then use the pre-existing time intelligence functions to get the duration from starttime and endttime columns.

Sounds very logical, will try this one thanks! Nevertheless, I am not sure what sort of intelligence functions you are talking about. 

Here's a screenshot

Gabriel_Walkman_0-1618384446549.png

 

View solution in original post

amitchandak
Super User IV
Super User IV

@AndrejBI , do you have any column like session id?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Yes, I do! 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors