Calculating difference in time in different rows

[BorisTPL]

Hi all, I need some help with my calculation of the lead time for our shop. I have different date/time values for certain status updates and want to find the time difference between te statusses with the same WorkorderID.

I want to make a calculation where I can subtract the duration that the workorderID had status "Estimate" from the total time between "Open" and "Finished", as we do not have any influence on the total time the workorder has that status because we have to wait for a reply from the customer. I tried to do this by using the Earlier() function but were not in able to get the proper result. Would be great if someone can help me with the DAX code to achieve this. 

Used code:

Duration =

VAR LastDateTime =

MINX(

FILTER(

'WorkorderRealTime',

'WorkorderRealTime'[workorderID] = EARLIER('WorkorderRealTime'[workorderID]) //&&

//EARLIER('WorkorderRealTime'[WorkorderStatus_name]) = "Open" &&

//'WorkorderRealTime'[WorkorderStatus_name] = "Finished"

),

'WorkorderRealTime'[TimeStatusChange])

RETURN IF(LastDateTime<>BLANK(), FORMAT(WorkorderRealTime[TimeStatusChange] - LastDateTime, "HH:MM:SS"))

[BorisTPL · Accepted solution]

So if anyone ever needs it: I fixed it by creating an index column per workorderID and used the following code:

Duration In Minutes =

VAR NextRowIndex = 'WorkorderRealTime'[Index] + 1

RETURN

DATEDIFF (

'WorkorderRealTime'[TimeStatusChange],

CALCULATE (

VALUES('WorkorderRealTime'[TimeStatusChange]),

FILTER ( ALL ( 'WorkorderRealTime' ), 'WorkorderRealTime'[Index] = NextRowIndex), WorkorderRealTime[workorderID] = EARLIER(WorkorderRealTime[workorderID])

),

MINUTE

)

[BorisTPL]

Dear @v-henryk-mstf , thank you for your reply.

I tried using your code to check if it would result in the desired output, but unfortunalty it does not. I get the same output as you have posted above but this is not the amount of minutes that it took for each status to get to the next one. This calculates the amount of minutes between status estimate and another status.
My desired output would look like this: 

So what I think could be a solution, is taking the Datediff(Next Status change, current status change) instead of looking at the last status change. However I can't find a similar function like Earlier() but in the opposite direction, so if you know a way how to create a variable that takes the Next status change instead of the last status, I would be more than happy to hear

Also, the workorders do no always follow the same 'route'. This one is just an example. Sometimes there is not even a status 'estimate', so it would be better not too specify what the status_name is in the calculation. It should just look at the time diff between the next and the current status, regardless of what status it is.

[v-henryk-mstf]

Hi @BorisTPL ,

According to your needs, I did the following test, first get the time corresponding to the Estimate field, and then get the difference in turn, refer to the following:

Column =
VAR last_time =
    CALCULATE (
        MAX ( 'Table'[TimeStatusChange] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[workerID] = EARLIER ( 'Table'[workerID] )
                && 'Table'[workorderStatus_name] = "Estimate"
        )
    )
RETURN
    DATEDIFF ( last_time, 'Table'[TimeStatusChange], MINUTE )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BorisTPL]

The second one almost did it! However, the time is not in the correct row as shown below:

The time the workorder had status "Estimate" (5669 minutes) is shown in the row with status "Under repair". Do you maybe have a tip how to correct the formula to shift it to the right row? Thank you @amitchandak 

Code:

Duration in minutes =

var _max =maxx(filter(WorkorderRealTime, [workorderID] = earlier([workorderID]) && [TimeStatusChange] <earlier([TimeStatusChange])),[TimeStatusChange])

return

datediff(_max,[TimeStatusChange] , minute)

[amitchandak]

@BorisTPL , try column like

new column =
var _max =maxx(filter(Table, [workorder ID] = earlier([workorder ID]) && [datetime] <earlier([timestatus change])),[timestatus change])
return
[timestatus change] -_max

or

new column =
var _max =maxx(filter(Table, [workorder ID] = earlier([workorder ID]) && [datetime] <earlier([timestatus change])),[timestatus change])
return
datediff(_max,[timestatus change] , minute)