cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GunnerJ Member
Member

Time to complete a task. End of one to the end of another.

I have Tasks within a service order that have completion times. I'm needing to find the time between one completion time and the next tasks completion time. So the time between comp of Task A and the comp of Task B would be the time Task B took to complete. There are sequence numbers to help order things. 

 

Here's a sample of my data. In the calculation I'd need to only view one service order at a time, order the Task_Seq_Nbrs then find the time between the tasks using the time with a work_event_cd2 of "COMP". I know if has a start and stop CD but for this particular calculation that isn't correct. Only time I'd use start is if it was seq# 1 that didn't have a prior task to look for a "COMP".

In the sample below SEQ #2 took 2 seconds to complete (automated task).

Time between Completions.PNG

 

Any help would be appreciated and if I need to add anymore context please let me know!

Thanks!

 

Also, this code gives the time between the task's comp time and the start of the service order which actually gets sequence 1 correct but the rest wrong. The time between these values is correct but the totals are not. It returns the value 24 seconds for seq 1 and 26 seconds for sequence 2. The difference between those is 2 seconds which is correct. I just need to find a way to get it to look at the last COMP instead of start of the service order. 

 

Task Time =
CALCULATE (
DATEDIFF (
MIN ( Workflow1[BI_EVENT_DT_TM] ),
MAX ( Workflow1[BI_EVENT_DT_TM] ),
SECOND
),
FILTER (
ALL ( Workflow1 ),
Workflow1[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER ( Workflow1[BI_WRKFLW_TASK_SEQ_NBR] )
&& Workflow1[BI_SO_NBR] = EARLIER ( Workflow1[BI_SO_NBR] )
&& Workflow1[BI_TASK_CD] = EARLIER ( Workflow1[BI_TASK_CD] )
&& Workflow1[BI_WORK_EVENT_CD] = "COMP"
)
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Time to complete a task. End of one to the end of another.

@GunnerJ as discussed over the call, you can add following measure and/or column and test it in your model

 

Measure

 

Individual Task Time = 
VAR __currentSequenceNumber = SELECTEDVALUE(  Workflow[BI_WRKFLW_TASK_SEQ_NBR] ) --current sequence number
VAR __previousSequenceNumber = __currentSequenceNumber - 1
VAR __currentSequenceTaskTime = SELECTEDVALUE( Workflow[Task Time] )
VAR __previousSequenceTaskTime = 
CALCULATE ( 
    MAX ( Workflow[Task Time] ), 
    ALLEXCEPT ( Workflow, Workflow[BI_SO_NBR] ),
    Workflow[BI_WRKFLW_TASK_SEQ_NBR] = __previousSequenceNumber
)
VAR __actualTime =
IF ( 
    __currentSequenceNumber = 1, 
    __currentSequenceTaskTime, 
    __currentSequenceTaskTime - __previousSequenceTaskTime
)
RETURN
__currentSequenceNumber

 

Column

 

Individual Task Time Col = 
Workflow[Task Time] - 
CALCULATE ( 
    MAX ( Workflow[Task Time] ), 
    ALLEXCEPT ( Workflow, Workflow[BI_SO_NBR] ),
    Workflow[BI_WRKFLW_TASK_SEQ_NBR] < EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Time to complete a task. End of one to the end of another.

@GunnerJ 

 

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Finding-the-previos-occurrence-within-same-table-based-on-o...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GunnerJ Member
Member

Re: Time to complete a task. End of one to the end of another.

@v-chuncz-msft 

 

I can see the similarity of the two posts but I'm having trouble modifying to meet my exact requirements. Oddly enough the EARLIER function won't work for me when I write it out. I get a message that it can't find the SEQ Number column. Any tips on what I could modify?

Super User
Super User

Re: Time to complete a task. End of one to the end of another.

@GunnerJ as discussed over the call, you can add following measure and/or column and test it in your model

 

Measure

 

Individual Task Time = 
VAR __currentSequenceNumber = SELECTEDVALUE(  Workflow[BI_WRKFLW_TASK_SEQ_NBR] ) --current sequence number
VAR __previousSequenceNumber = __currentSequenceNumber - 1
VAR __currentSequenceTaskTime = SELECTEDVALUE( Workflow[Task Time] )
VAR __previousSequenceTaskTime = 
CALCULATE ( 
    MAX ( Workflow[Task Time] ), 
    ALLEXCEPT ( Workflow, Workflow[BI_SO_NBR] ),
    Workflow[BI_WRKFLW_TASK_SEQ_NBR] = __previousSequenceNumber
)
VAR __actualTime =
IF ( 
    __currentSequenceNumber = 1, 
    __currentSequenceTaskTime, 
    __currentSequenceTaskTime - __previousSequenceTaskTime
)
RETURN
__currentSequenceNumber

 

Column

 

Individual Task Time Col = 
Workflow[Task Time] - 
CALCULATE ( 
    MAX ( Workflow[Task Time] ), 
    ALLEXCEPT ( Workflow, Workflow[BI_SO_NBR] ),
    Workflow[BI_WRKFLW_TASK_SEQ_NBR] < EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)