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:

 

DateNameDepartmentComingGoing
13.06.2022Stefani VilevaSales15:0016:00
13.06.2022Stefani VilevaSales10:0014:45
13.06.2022Stefani VilevaSales08:0009: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

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

     

4 Replies

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

     

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    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 ))

     

    • FarhanAhmed's avatar
      FarhanAhmed
      Community Champion
      4 years ago

       

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