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

[Anonymous]

Hi,

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

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

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

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

Here is my test table.

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

Result:

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.

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

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

Here is my test table.

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

Result:

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.

[Gabriel_Walkman]

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.

[Anonymous]

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

[Gabriel_Walkman · Accepted solution]

Here's a screenshot

[amitchandak]

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

[Anonymous]

Yes, I do!