cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

New to DAX--Assistance with Task Timing Needed

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!

2 REPLIES 2
Frequent Visitor

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 OnEventTodo TypeAdmin ID
5ea081130d4a7400076e754211/3/2020 20:17completedbill-data5f694b8391550a0012307e96
5ea081130d4a7400076e754211/3/2020 20:17openedbill-data5f694b8391550a0012307e96
5ed1a263cf927a0007cb635e11/3/2020 17:57completedbill-data5be4e4688934a461c50d51b1
5ed1a263cf927a0007cb635e11/3/2020 17:57openedbill-data5be4e4688934a461c50d51b1
5f5fc5cc955e81c312ecee0511/3/2020 19:13completedswap5f694b8391550a0012307e96
5f5fc5cc955e81c312ecee0511/3/2020 19:13openedswap5f694b8391550a0012307e96
5f8d7d5d83f8b143a599ca2211/3/2020 17:06completedbill-data5be4e4b28934a461c50d51b2
5f8d7d5d83f8b143a599ca2211/3/2020 17:05openedbill-data5be4e4b28934a461c50d51b2
5f945531b6eaa79719945d7e11/3/2020 22:36completedswap5be4e4688934a461c50d51b1
5f945531b6eaa79719945d7e11/3/2020 22:33openedswap5be4e4688934a461c50d51b1
5f9f2565fba032b789afa9b511/3/2020 21:16completedswap5f694b8391550a0012307e96
5f9f2565fba032b789afa9b511/3/2020 21:16openedswap5f694b8391550a0012307e96
5f9f264e9e471015ca3fd72a11/3/2020 21:17completedswap5f694b8391550a0012307e96
5f9f264e9e471015ca3fd72a11/3/2020 21:17openedswap5f694b8391550a0012307e96
5f9f2695fba032789cafaa0211/3/2020 21:18completedswap5f694b8391550a0012307e96
5f9f2695fba032789cafaa0211/3/2020 21:17openedswap5f694b8391550a0012307e96
5f9f27309e471063f13fd76511/3/2020 21:16completedswap5f694b8391550a0012307e96
5f9f27309e471063f13fd76511/3/2020 21:16openedswap5f694b8391550a0012307e96
5f9f27309e471063f13fd76511/3/2020 20:55completedswap5f694b8391550a0012307e96
5f9f27309e471063f13fd76511/3/2020 20:55openedswap5f694b8391550a0012307e96
5fa0aef7fba032862ab000be11/3/2020 18:08completedswap5be4e4688934a461c50d51b1
5fa0aef7fba032862ab000be11/3/2020 18:08openedswap5be4e4688934a461c50d51b1
5fa16d229e471026f6404b6e11/3/2020 19:15completedswap5eaad88dccae21001181386c
5fa16d229e471026f6404b6e11/3/2020 19:14openedswap5eaad88dccae21001181386c
5fa16d229e471026f6404b6e11/3/2020 17:57openedswap5be4e4688934a461c50d51b1
5fa16d229e471026f6404b6e11/3/2020 17:22openedswap5be4e4b28934a461c50d51b2
5fa17d9ffba032408ab0247b11/3/2020 22:53completedbill-data5be4e4688934a461c50d51b1
5fa17d9ffba032408ab0247b11/3/2020 22:49openedbill-data5be4e4688934a461c50d51b1
5fa17deafba0321f65b0249d11/3/2020 20:55completedswap5be4e4b28934a461c50d51b2
5fa17deafba0321f65b0249d11/3/2020 20:53openedswap5be4e4b28934a461c50d51b2
5fa18ae89e4710055040555b11/3/2020 17:06completedswap5be4e4b28934a461c50d51b2
Super User III
Super User III

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 

SU18_powerbi_badge

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.