filtering and calculating

[Anonymous]

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?

[Anonymous]

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]

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]

@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  🙂

[Anonymous]

Did my solution resolve your issue?
Thanks