Forum Discussion

DR's avatar
DR
New Member
9 years ago

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 sta...
Need Help
fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
9 years ago

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

DR's avatar
DR
New Member
9 years ago
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
  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    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...?  

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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