Forum Discussion

BorisTPL's avatar
BorisTPL
Frequent Visitor
5 years ago
Solved

Calculating difference in time in different rows

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...
Need Help
Tips and Tricks
  • BorisTPL's avatar
    BorisTPL
    5 years ago

    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's avatar
BorisTPL
Frequent Visitor
5 years ago

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.