New to DAX--Assistance with Task Timing Needed

[igrandey89]

Hello all,

I have been given the task of identifying the average task timings for my team. However, I keep getting stuck and could use some assistance 🙂

Essentially, there are 2 columns i'm focused on: Date/Time stamp & Event:

Completed Tasks are: Opened > Completed for the same Client ID (in that order). However, there are multiple employees complete tasks simultaniously and they do not always line up in order.

Essentially, I just need to grab the date/time stamp from the Created On column (when the event is opened) and then grab the difference between the timestamp and date when the event is "completed."

I'm fairly new to DAX so all the help is greatly appreciated!

[igrandey89]

Thank you for the reply @AIB. Here's the example data, below.

My expected result would be to capture the time it takes to complete tasks or "Todo Types."

• Each Auth ID (should be 2 duplicates for each, listed) corresponds with a completed task type.
• Created On indicates the Time and date in which the todo task was either started (opened) and completed.
• The updated by column indicates the employee who completed the task.
  
  I need to be able to identify the time it takes an employee to complete tasks, based on the opened (starting time) and the end (completed time) in the "Created On" Column. 
  
  The thing I cannot wrap my head around is the sequence of "opened -> completed" is not always in the proper order, as multiple todo types are being worked by multiple people at once, and there are also other event types (however I filtered these out, for now).

I also need to ensure the Auth ID and the Updated by IDs are the same (2 same Auth IDs and 2 same updated by) for both the opened and completed rows--to ensure i'm capturing the timing for each individual person

And finally, in the end, I simply want to convert the date/timestamp into seconds (the actual data includes additional digits on the time-stamp) and get the difference between the opened event time, and the completed event time. 

Auth ID	Created On	Event	Todo Type	Admin ID
5ea081130d4a7400076e7542	11/3/2020 20:17	completed	bill-data	5f694b8391550a0012307e96
5ea081130d4a7400076e7542	11/3/2020 20:17	opened	bill-data	5f694b8391550a0012307e96
5ed1a263cf927a0007cb635e	11/3/2020 17:57	completed	bill-data	5be4e4688934a461c50d51b1
5ed1a263cf927a0007cb635e	11/3/2020 17:57	opened	bill-data	5be4e4688934a461c50d51b1
5f5fc5cc955e81c312ecee05	11/3/2020 19:13	completed	swap	5f694b8391550a0012307e96
5f5fc5cc955e81c312ecee05	11/3/2020 19:13	opened	swap	5f694b8391550a0012307e96
5f8d7d5d83f8b143a599ca22	11/3/2020 17:06	completed	bill-data	5be4e4b28934a461c50d51b2
5f8d7d5d83f8b143a599ca22	11/3/2020 17:05	opened	bill-data	5be4e4b28934a461c50d51b2
5f945531b6eaa79719945d7e	11/3/2020 22:36	completed	swap	5be4e4688934a461c50d51b1
5f945531b6eaa79719945d7e	11/3/2020 22:33	opened	swap	5be4e4688934a461c50d51b1
5f9f2565fba032b789afa9b5	11/3/2020 21:16	completed	swap	5f694b8391550a0012307e96
5f9f2565fba032b789afa9b5	11/3/2020 21:16	opened	swap	5f694b8391550a0012307e96
5f9f264e9e471015ca3fd72a	11/3/2020 21:17	completed	swap	5f694b8391550a0012307e96
5f9f264e9e471015ca3fd72a	11/3/2020 21:17	opened	swap	5f694b8391550a0012307e96
5f9f2695fba032789cafaa02	11/3/2020 21:18	completed	swap	5f694b8391550a0012307e96
5f9f2695fba032789cafaa02	11/3/2020 21:17	opened	swap	5f694b8391550a0012307e96
5f9f27309e471063f13fd765	11/3/2020 21:16	completed	swap	5f694b8391550a0012307e96
5f9f27309e471063f13fd765	11/3/2020 21:16	opened	swap	5f694b8391550a0012307e96
5f9f27309e471063f13fd765	11/3/2020 20:55	completed	swap	5f694b8391550a0012307e96
5f9f27309e471063f13fd765	11/3/2020 20:55	opened	swap	5f694b8391550a0012307e96
5fa0aef7fba032862ab000be	11/3/2020 18:08	completed	swap	5be4e4688934a461c50d51b1
5fa0aef7fba032862ab000be	11/3/2020 18:08	opened	swap	5be4e4688934a461c50d51b1
5fa16d229e471026f6404b6e	11/3/2020 19:15	completed	swap	5eaad88dccae21001181386c
5fa16d229e471026f6404b6e	11/3/2020 19:14	opened	swap	5eaad88dccae21001181386c
5fa16d229e471026f6404b6e	11/3/2020 17:57	opened	swap	5be4e4688934a461c50d51b1
5fa16d229e471026f6404b6e	11/3/2020 17:22	opened	swap	5be4e4b28934a461c50d51b2
5fa17d9ffba032408ab0247b	11/3/2020 22:53	completed	bill-data	5be4e4688934a461c50d51b1
5fa17d9ffba032408ab0247b	11/3/2020 22:49	opened	bill-data	5be4e4688934a461c50d51b1
5fa17deafba0321f65b0249d	11/3/2020 20:55	completed	swap	5be4e4b28934a461c50d51b2
5fa17deafba0321f65b0249d	11/3/2020 20:53	opened	swap	5be4e4b28934a461c50d51b2
5fa18ae89e4710055040555b	11/3/2020 17:06	completed	swap	5be4e4b28934a461c50d51b2

[AlB]

Hi @igrandey89 

Can you please show with an example based on data what you are looking for, with the expected result? It's not quite clear right now. Please also share a sample of your data on text-tabular format rather than a screen cap so that it can be readily copied and used for tests.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers