Forum Discussion

stefani_vileva's avatar
stefani_vileva
Resolver II
4 years ago
Solved

Calculating breaks in working time

Hello everyone,   I have problem solving the time duration between times of two different rows. For example, I have the following table:   Date Name Department Coming Going 13.06.2022 ...
Need Help
  • FarhanAhmed's avatar
    FarhanAhmed
    4 years ago

    you can create a calculated column to get the Last out for particular day and then take the difference between 2 times

     

    Last Out = 
Var dt = 'Table'[Date]
Var nm = 'Table'[Name]
Var dpt = 'Table'[Department]
var cmg = 'Table'[Coming]
RETURN 

CALCULATE(MAX('Table'[Going]),FILTER('Table','Table'[Date]=dt && 'Table'[Name]=nm && 'Table'[Department]=dpt && 'Table'[Coming]<cmg ))

     

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Hi stefani_vileva ,

 

You need to create a index column from the power query then you can create a column as below:-

Column =
VAR _current_index = [Index]
VAR _prev_index = [Index] - 1
VAR _coming_time =
    CALCULATE (
        MAX ( 'Table (3)'[Coming] ),
        FILTER (
            'Table (3)',
            'Table (3)'[Index] = _prev_index
                && 'Table (3)'[Date] = EARLIER ( 'Table (3)'[Date] )
                && 'Table (3)'[Name] = EARLIER ( 'Table (3)'[Name] )
        )
    )
RETURN
    DATEDIFF ( 'Table (3)'[Going], _coming_time, MINUTE )

Output:-