cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DR
New Member

calculate difference between time stamps for unique ID

Grateful for help with the following issue.

 

I have the following table with unique job IDs (score origin) and want to compare the difference in time between when that job ID is in a different state (metric column). More specifically the difference in time between when it is 'ACCP' (accepted) and when it is 'ONST' (on site).

 

 Metric.PNG

I have created new tables to try and make it easier to compare the difference in time stamps, but to no avail. Grateful if anyone could point me in the right direction, preferably if I was able to create a new column with time difference betewen when the job is 'accepted' (ACCP) and when someone is actually 'on site' (ONST).

 

Many thanks

7 REPLIES 7
v-yulgu-msft
Microsoft
Microsoft

Hi @DR,

 

Please try this workaround:

 

Create a new table.

Table_1 =
SELECTCOLUMNS (
    FILTER ( 'time difference', 'time difference'[metric] = "ONST" ),
    "score time", 'time difference'[score time],
    "score origin", 'time difference'[score origin]
)

In original table, add a calculated column which displays the time difference between "ACCP" and "ONST".

time diff =
DATEDIFF (
    'time difference'[score time],
    IF (
        'time difference'[metric] = "ACCP",
        LOOKUPVALUE (
            Table_1[score time],
            Table_1[score origin], 'time difference'[score origin]
        ),
        BLANK ()
    ),
    SECOND
)

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fhill
Super User
Super User

I usually tell people NOT to pivot their data, but the quickest / easiest way I can think to resolve this is to put your data into a Pivot table.   First, I had to mass change your "T" in your time column to a blank so PowerBI can easily identify it as a Date Time column.

 

With the **Metric** column selected in Query Editors, choose 'Pivot Column' under 'Transform' Tab.  Make sure under Advanced NOT to Summarize.

 

Capture.PNG

 

Now you can easily use DateDiff to calucatle time difference as needed...  FOrrest

 

Capture2.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to be a Super User helping give back to the community!
Thank You!




Very much obliged for this.

 

However, I notice that I get a number of columns with errors or blanks once I have pivoted. Is this because there are some duplications where an individual has register 'ACCP' twice for the same ID?

DOH!  I didnt' noticed the duplicates in your data.  Can you copy/past some sample data (instead of the picture) so we can quickly dump it into Power BI and play with the manipulation...  ?

 

FOrrest




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to be a Super User helping give back to the community!
Thank You!




score timescore originmetric
2017-06-24T20:10:04005056B614ED1ED796A191BDD043A0DAACCP
2017-06-24T21:43:38005056B614ED1ED796A191BDD043A0DACMPN
2017-06-24T20:10:08005056B614ED1ED796A191BDD043A0DAENRT
2017-06-24T20:10:14005056B614ED1ED796A191BDD043A0DAONST
2017-06-24T19:52:31005056B614ED1ED796A191BDD043A0DARCVD
2017-06-24T21:04:05005056B614ED1ED796A191BDD043A0DAWIP
2017-06-27T06:56:46005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T06:59:29005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T14:35:38005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T14:40:51005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T15:47:25005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T15:49:41005056B614ED1ED796B988341EDC00DARCVD
2017-06-27T18:02:26005056B614ED1ED796B988341EDC00DARCVD
2017-05-15T17:08:17005056B61C081ED78EB0DC4AEE5500D8ACCP
2017-05-15T17:09:24005056B61C081ED78EB0DC4AEE5500D8ENRT
2017-05-15T17:27:43005056B61C081ED78EB0DC4AEE5500D8ONST
2017-05-15T17:05:35005056B61C081ED78EB0DC4AEE5500D8RCVD
2017-05-15T18:25:20005056B61C081ED78EB0DC4AEE5500D8WIP
2017-05-15T20:23:45005056B61C081ED78EB2523FFFCBE0D8ACCP
2017-05-15T20:23:50005056B61C081ED78EB2523FFFCBE0D8ENRT
2017-05-16T03:55:53005056B61C081ED78EB2523FFFCBE0D8NCMF
2017-05-15T20:24:01005056B61C081ED78EB2523FFFCBE0D8ONST
2017-05-15T20:14:47005056B61C081ED78EB2523FFFCBE0D8RCVD
2017-05-15T20:24:27005056B61C081ED78EB2523FFFCBE0D8WIP
2017-05-16T21:42:33005056B61C081ED78ECEA29F86DEE0D8ACCP
2017-05-16T22:45:37005056B61C081ED78ECEA29F86DEE0D8CMPN
2017-05-16T21:42:42005056B61C081ED78ECEA29F86DEE0D8ENRT
2017-05-16T22:04:09005056B61C081ED78ECEA29F86DEE0D8ONST
2017-05-16T21:37:00005056B61C081ED78ECEA29F86DEE0D8RCVD
2017-05-16T22:41:28005056B61C081ED78ECEA29F86DEE0D8WIP
2017-05-18T02:18:23005056B61C081ED78EECB9F8A64240D8ACCP
2017-05-18T03:24:16005056B61C081ED78EECB9F8A64240D8CMPN
2017-05-18T02:18:25005056B61C081ED78EECB9F8A64240D8ENRT
2017-05-18T02:38:02005056B61C081ED78EECB9F8A64240D8ONST
2017-05-18T02:12:24005056B61C081ED78EECB9F8A64240D8RCVD
2017-05-18T02:47:31005056B61C081ED78EECB9F8A64240D8WIP
2017-05-18T03:59:55005056B61C081ED78EEDE57F4D9720D8ACCP
2017-05-18T04:49:52005056B61C081ED78EEDE57F4D9720D8CMPN
2017-05-18T04:00:00005056B61C081ED78EEDE57F4D9720D8ENRT
2017-05-18T04:02:21005056B61C081ED78EEDE57F4D9720D8ONST
2017-05-18T03:48:50005056B61C081ED78EEDE57F4D9720D8RCVD
2017-05-18T03:57:48005056B61C081ED78EEDE57F4D9720D8RCVD
2017-05-18T04:45:50005056B61C081ED78EEDE57F4D9720D8WIP
2017-05-18T08:29:41005056B61C081ED78EF139996DD220D8ACCP
2017-05-18T20:57:14005056B61C081ED78EF139996DD220D8CMPN
2017-05-18T08:29:44005056B61C081ED78EF139996DD220D8ENRT
2017-05-18T08:29:52005056B61C081ED78EF139996DD220D8ONST
2017-05-18T07:39:20005056B61C081ED78EF139996DD220D8RCVD
2017-05-18T20:46:41005056B61C081ED78EF139996DD220D8WIP

Hi @DR,

 

Have you worked it out? If yes, please kindly mark the helpful reply as an answer so that it can benefit more users. If you still have any question, please feel free to ask.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You were right about the Errors being duplicates.  You'll have to descide how you want to handle that data...  Since your values are times, Min is Earliest and Max is Latest.  The 'null's are blanks in your original data, so ignore them, or go back to your data origin to resolve?

 

I think the biggest now is 'What if I want First RCVD but Last CMPN???  I don't have an answer for you on that one, sorry...  Hopefully someone else jumps on this and might be able to build something simular with custom columns in DAX or something...?  

 

 

Capture.PNGCapture2.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to be a Super User helping give back to the community!
Thank You!




Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors