Total Login Time (Accounting for Overlapping Periods)

[G_Russo]

Hello, I'm looking to calculate the total login time for my users. Where I'm stuck is they sometimes need to access different servers while they're here and I end up logging the same periods multiple times. My data looks like this:

I made a report with example data and the expected output: https://drive.google.com/file/d/1OvcE6Tw7nT2maus3GylRY3JjSi41Wvzm/view?usp=sharing

Thank you for your help.

[v-shex-msft]

HI @G_Russo,

You can add a column to your table to get the different from start end time and convert them to total seconds.

DATEDIFF function (DAX) - DAX | Microsoft Learn

After these steps, you can use user id as condition to aggregate thee second values and convert them back to the time string.(current power bi data model table does not support duration type values)

Regards,

Xiaoxin Sheng

[G_Russo]

Thank you for your reply @v-shex-msft ,
If I understand correctly this would only give me the total sum of time difference, that's how I originally calculated the HOURS column. What I'm looking for is a way to account for simultaneous logins such that if I'm logged into two servers at once for say 4 hours, the result is 4 hours and not 8.

[v-shex-msft]

Hi @G_Russo,

So you means these records may include intersection parts so you need to exclude the duplicate calculations?

If that is the case, you can filter the records based on current group and aggregate them to get the min and max time to calculate the unique difference. Then you can summary these results to get the correct total.

All the secrets of SUMMARIZE - SQLBI

Regards,
Xiaoxin Sheng

[G_Russo]

I thought of this but it doesn't account for gaps in between login periods.