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
DR's avatar
DR
New Member
9 years ago

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?

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