Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

filtering and calculating

Hello,

I have this table:

 

And My goal is to calculate time difference between differents statuses. OldValue and NewValue represent old and new statuses. It is important to sort ascending CretedDate column before any calculation. And the last important note is ParentId it is id of the company. Right now I have just filtered one company, but the trick is that data is messy as it is in the pic above.

 

So the question is how to calculate time difference between 2 stages for the same company?

 

General Comment
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi Anonymous 

    what are the other fields in the table? is there only one time period? Do you also get the status change date for the new value? What's the source you're extracting data from?

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous 

       - This is all fields that I have.
       - Yes only one time period, so it works this way: 5/31/2019 5:47 C-complete status was choosed, and several actions was done maybe automaticly as a trigger, then 5/31/2019 8:21 manager choosed  d-compliance status, then 5/31/2019 10:52 from d-compliance manager choosed c-reporting/testing status and so on.
       - Source is Salesforce DB

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous  You need to split the date column into two parts, One to have the Date and the other column will be TIMEx

        In the power query GROUP BY

        In GROUP BY = Date

        Add two new columns 

        MinH     MIN    TIMEx

        MaxH    MAX    TIMEx

        once done select OK

         

        Create a Custom Column ( this is your final measure) in Power Q --> add the below code.

        =Duration.Hours([MaxH]-[MinH])

        All good  :)