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

    )
     
     
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
5 years ago

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.