Calculating breaks in working time

[stefani_vileva]

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	Stefani Vileva	Sales	15:00	16:00
13.06.2022	Stefani Vileva	Sales	10:00	14:45
13.06.2022	Stefani Vileva	Sales	08:00	09:35

My goal is to calculate the time of the breaks between this working times, i.e. I have break from 09:35 - 10:00 which is 25 minutes break and 14:45 - 15:00, 15 minutes break. I don't want to find the overall break time for example in our case 40 minutes, but for every break individually.

Thanks in advance.

Kind regards,

Stefani Vileva

[FarhanAhmed · Accepted solution]

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 ))

[FarhanAhmed]

Time Diff = IF(ISBLANK('Table'[Last Out]),BLANK(),'Table'[Coming]-'Table'[Last Out])

[stefani_vileva]

Hello @FarhanAhmed 

Thanks a lot, it solved my problem.

Kind regards,

Stefani

[Samarth_18]

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:-