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

[GunnerJ]

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

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"

)

)

[v-chuncz-msft]

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

[GunnerJ]

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

[parry2k · Accepted solution]

@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] )
)