Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago
Solved

Calculate Time Between

Hi all,   How can create the column 'Break' below?   It calculates the time between the end of the shift and start of the next one. They can be on different dates but have to be the next day work...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi HenryJS ,

     

    Start_time = 
VAR __start = MAX(Sheet7[Start])
RETURN
DATE(2020, 1, 2) + __start
    End_time = 
VAR __end = 
CALCULATE(
    MAX([End]),
    FILTER(
        ALL(Sheet7),
        Sheet7[Timesheet ID] = MAX(Sheet7[Timesheet ID]) && Sheet7[Date] < MAX(Sheet7[Date]) 
    )
)
RETURN
IF(
    __end <> BLANK(),
    DATE(2020,1,1) + __end,
    BLANK()
)
    Measure 2 = 
IF(
    [End_time] <> BLANK(),
   [Start_time] - [End_time],
    TIME(0,0,0)
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
5 years ago

HenryJS , Try new columns like

 

create new columns
start time = [Date] + [start]
End time = [Date] + [end]

Break = maxx(flter(Table, [Timesheet ID] =earlier([Timesheet ID]) && [Date]<earlier([date])),[End time]) -[start time]

  • HenryJS's avatar
    HenryJS
    Post Prodigy
    5 years ago

    amitchandak thanks that works!

     

    However on the first instance it calculates a break time even though there's no shift before?

     

    The one highlighted red should be 0