Forum Discussion

manyerukebenn's avatar
manyerukebenn
Regular Visitor
6 years ago

Date Difference for two rows

I am having problems calculating date difference for the below scenario. I want to calculate the total time spent on a particular job by a mechanic. When the mechanic start and pause we should calcul...
Chthonian's avatar
Chthonian
Helper III
6 years ago

Hi manyerukebenn 

I am a bit of an amateur and there would be a much more sophisticated way to achieve this  but this solution worked for me 🙂

 

Job Duration = 
    VAR JobStart = CALCULATE(MIN(Table1[Activity Time]), Table1[Status ] = "Started")
    VAR BreakStart = CALCULATE(MIN(Table1[Activity Time]), Table1[Status ] = "Paused")
    VAR BreakEnd = CALCULATE(MAX(Table1[Activity Time]), Table1[Status ] = "Started")
    VAR JobEnd = CALCULATE(MAX(Table1[Activity Time]), Table1[Status ] = "Stopped")
RETURN
CALCULATE(
    DATEDIFF(JobStart,JobEnd,MINUTE) - DATEDIFF(BreakStart,BreakEnd,MINUTE)
)

 

PS: Ignore the 'DocEntry' figure, forgot to remove the quick sum calc 😁