Working with timestamp data - looking at average duration. Please help!

[clewis]

Hi everyone!

I have a table of network access data.  I have IP addresses and then the date and time of when the device get on and off the network throughout the day.  There are multiple associations for each device everyday due to different AP devices. 

I want to look at the IP address on a given day, and get the Max sign off time (example Sept 8th 5:15 PM) and subtract the min sign on time (example: Sept 8th 8:03 AM) to understand approximately how long that person was at the office on a given day.  I want to plot this daily average # of hours over time.  Can you help with this one?

Below, is a simple example.  Users 1a and 2b each have two network associations on 9/8/2021.  I want to calculate their durations on that day by subtracting their earliest timestamp from their latest and take the average for all devices.

Log Timestamp	IP ADDRESS	Visit Start Timestamp	Visit End Timestamp
2021-09-08 00:18:42UTC	1a	2021-09-08 00:14:41UTC	2021-09-08 00:17:39UTC
2021-09-08 00:55:21UTC	2b	2021-09-08 00:34:19UTC	2021-09-08 00:44:28UTC
2021-09-08 00:18:42UTC	1a	2021-09-08 02:14:41UTC	2021-09-08 02:17:39UTC
2021-09-08 00:55:21UTC	2b	2021-09-08 00:44:28UTC	2021-09-08 00:54:28UTC
2021-09-07 11:18:44UTC	3c	2021-09-07 11:17:26UTC	2021-09-07 14:18:36UTC
2021-09-07 11:24:10UTC	1a	2021-09-07 11:24:18UTC	2021-09-07 15:24:04UTC

9/8/2021 average duration = 1.1 hours

9/7/2021 average duration = 3.5 hours

[v-luwang-msft]

Hi @clewis ,

Could you pls share your pbix file ,and remove confidential data.

Best Regards

Lucien

[clewis]

Hi @v-luwang-msft were you able to access the file?

[clewis]

Hi @v-luwang-msft I've cleaned up the file and ready to share it.  How do i do that?  I dont see an attachment icon.

[v-luwang-msft]

Hi @clewis ,

Share your link by a public link.

Best Regards

Lucien

[v-luwang-msft]

Hi @clewis ，

In my opinion, you can count the number of unduplicated IPs in the average duration.

In the sample ,the same measure（May have to be rounded up or down, depending on whether you need） with distincount Ip.

Best Regards

Lucien

[clewis]

Hi @v-luwang-msft 

1.  I dont think I understand how to do that.  Can you walk me through plotting the duration distribution in this format?

2. This formula is not calculating properly.  See below sample data for just Sept 20.  The formula is calculating the daily average to be 5.17 hours but it should be 3.81 hours.  The table below is calculating correctly for each individual IP address on a given day.  

[v-luwang-msft]

Hi  @clewis ,

Test below :

Create the data column:

DATE1 = FORMAT('Table'[Log Timestamp],"yyyymmdd")

Then create the below measure:

test = var datediff1=DATEDIFF(
CALCULATE (
        MIN ( 'Table'[Visit Start Timestamp] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[IP ADDRESS] = MAX ( 'Table'[IP ADDRESS] )
                && FORMAT ( 'Table'[Log Timestamp], "yyyymmdd" )
                    = FORMAT ( MAX ( 'Table'[Log Timestamp] ), "yyyymmdd" )
        )),CALCULATE (
    MAX ( 'Table'[Visit End Timestamp] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[IP ADDRESS] = MAX ( 'Table'[IP ADDRESS] )
            && FORMAT ( 'Table'[Log Timestamp], "yyyymmdd" )
                = FORMAT ( MAX ( 'Table'[Log Timestamp] ), "yyyymmdd" )
    )),
MINUTE)/60     return datediff1

Measure = AVERAGEX(FILTER(ALL('Table'),'Table'[DATE1]=MAX('Table'[DATE1])),[test])

Final output:

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[clewis]

@v-luwang-msft 

[clewis]

Hi @v-luwang-msft 

Can you access this?

https://www.dropbox.com/s/f90fe6r43ntzkte/Test1.pbix?dl=0

[goncalogeraldes]

Hello there @clewis ! I hope I understood your question correctly. Try this:

Average =
var _min = MIN( Table[Start Timestamp] )
var _max = MAX( Table[End Timestamp] )
var _log = SELECTEDVALUE( Table[Log Timestamp] )

return
CALCULATE(
AVERAGEX( Table, _min + _max ),
Table[Log Timestamp] = _log
)

  If the CALCULATE() does not work try with an IF()!

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

[clewis]

Hi Goncalo!

I need to find the difference between the last end timestamp and first start time stamp for each unique IP address each day. Then i need to take the average and plot the distribution of durations.  Do you have any ideas on how to do that?  It doesnt look like the above takes into consideration the different users.

Thank you!

Caiti