Help with Time interval data

[condo]

Looking for some help-

I am trying to display a productivity metric using a user time log. The time log table (sample below) is my data source and shows when a user opens a window, when they close that window, when they open the next window, etc.

I can pull a few different things. First, I can pull earliest start date, and latest end date by day. This will show me how long someone is logged into the system each day. My issue that I cannot figure out is how to determine how many of those hours are ‘active.’ For example, in the below table, see the last two rows where the end time of 10:27 AM, followed by a start time of 10:44. That is a 16 minute gap in which there was no activity. Simply summing the total duration of each line will not work as users can have multiple windows open at a time.  

start_date	end_date	User	window
11/5/2019 10:10:32 AM	11/5/2019 10:26:57 AM	John Smith	Interaction
11/5/2019 10:26:57 AM	11/5/2019 10:27:02 AM	John Smith	Search window
11/5/2019 10:27:02 AM	11/5/2019 10:27:15 AM	John Smith	detail window
11/5/2019 10:27:15 AM	11/5/2019 10:27:19 AM	John Smith	detail window
11/5/2019 10:27:29 AM	11/5/2019 10:27:42 AM	John Smith	detail window
11/5/2019 10:44:20 AM	11/5/2019 10:55:32 AM	John Smith	Interaction

My first try was to create a 24 hour calendar table (by day) in increments of 5 minutes. I would like to be able to break down a day into a 24 hour period and show what increments was that person active and what increments was that person inactive. Time table example below.

Date	Time	DateTime	Active/Inactive
11/5/2019	10:10:00 AM	11/5/2019 10:10:00 AM	Inactive
11/5/2019	10:15:00 AM	11/5/2019 10:15:00 AM	Active
11/5/2019	10:20:00 AM	11/5/2019 10:20:00 AM	Active
11/5/2019	10:25:00 AM	11/5/2019 10:25:00 AM	Active
11/5/2019	10:30:00 AM	11/5/2019 10:30:00 AM	Inactive
11/5/2019	10:35:00 AM	11/5/2019 10:35:00 AM	Inactive
11/5/2019	10:40:00 AM	11/5/2019 10:40:00 AM	Inactive
11/5/2019	10:45:00 AM	11/5/2019 10:45:00 AM	Active
11/5/2019	10:50:00 AM	11/5/2019 10:50:00 AM	Active

So what I was trying to do was create a measure/column that in the date table that would say.. John smith was active or inactive if that time in the date table was in one of those increments on the time log table- as you see in the last column (but i can't get it to work)

This was just one idea of how to accomplish the task and may not be the best or only way, but hopefully illustrates the issue and what I am trying to accomplish. If I can accomplish with a measure and no extra date table that works also…

Thanks so much for any ideas and thoughts!

[v-eachen-msft · Accepted solution]

Hi @condo ,

You could create an index column in the query editor at first.

Then refer to the following DAX to get previous time and use DATEDIFF() function to get the time diff.

Column =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( Sheet2[start_date], 1 ),
        FILTER (
            Sheet2,
            Sheet2[Index]
                = EARLIER ( Sheet2[Index] ) + 1
                && Sheet2[User] = EARLIER ( Sheet2[User] )
        )
    )
RETURN
    DATEDIFF ( a, Sheet2[end_date], MINUTE )

Here is the result.