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

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.

 

 

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

 

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