Calculate Time Between

[HenryJS]

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 worked (date column).

This also has to correlate to Timesheet ID.

Timesheet ID	Date	Start	End	Break
144699	02/09/2020	07:30	17:00	0
144699	03/09/2020	07:30	17:00	14:30
144699	06/09/2020	07:30	17:00	14:30
144700	01/09	08:00	17:00	0
144700	05/09	08:00	17:00	09:00

[v-lionel-msft · Accepted solution]

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]

@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]

@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

[Greg_Deckler]

@HenryJS See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

In your case you would use DATEDIFF between your row and the previous row once you extract the necessary values.