Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Timestamp data - average duration - please help!

  • 1.  Timestamp data - average duration - please help!

    Posted Sep 09, 2021 09:41 AM
    Hi Everyone,

    Hi - I have a similar problem I hope you can help me with!  I have a table of network access data.  I have IP addresses and then the date and time of when the device gets on and off the network throughout the day.  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 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 the average duration on that day by subtracting their earliest timestamp from their latest and taking the average.

    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
    1a 02:03
    2b 00:10
    9/8/21 average duration 1.1 hrs
    3c 03:01
    1a 04:00
    9/7/21 average duration 3.5 hrs

    K lewis

  • 2.  RE: Timestamp data - average duration - please help!

    Posted Sep 11, 2021 11:29 AM
    Hi @K lewis,

    Since you want to visualize a daily value, I'm assuming you have a proper date table in your model with a Dates[Date] column that has a 1:M relationship to your Log table.
    See if you can get something like this to work for you.

    I hope this is helpful

    Melissa de Korte