cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azat Regular Visitor
Regular Visitor

filtering and calculating

Hello,

I have this table:

image.png

 

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?

 

filtered.PNG

4 REPLIES 4
Highlighted
LW Member
Member

Re: filtering and calculating

hi @Azat 

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

Azat Regular Visitor
Regular Visitor

Re: filtering and calculating

Hi @LW 

 - 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 DBimage.png

LW Member
Member

Re: filtering and calculating

@Azat  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  Smiley Happy

LW Member
Member

Re: filtering and calculating

Did my solution resolve your issue?
Thanks

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 321 members 3,513 guests
Please welcome our newest community members: