Time between shift finish time and next shift start time

[Qotsa]

Hi,

Trying to find the "Break Time" between the shift finish time & the next shift start time per employee.

Attached is an excample of the shifts worked by one employee in a typical day.

Sample File 

[Qotsa]

@CNENFRNL I would like the results from your calculated column 'shifted' up a row.

Instead of the first row blank, the last should be blank.
I've been tinkering with your formula but havebeen unable to achieve this.

Time Between Calls =
VAR __prev =
MAXX (
TOPN (
1,
FILTER (
'powerbi114 view_finance',
'powerbi114 view_finance'[carer_id]
= EARLIER ( 'powerbi114 view_finance'[carer_id] )
&& 'powerbi114 view_finance'[Date] = EARLIER ( 'powerbi114 view_finance'[Date] )
&& 'powerbi114 view_finance'[Finish Time]
<= EARLIER ( 'powerbi114 view_finance'[Start Time] )
),
'powerbi114 view_finance'[Finish Time]
),
'powerbi114 view_finance'[Finish Time]
)
RETURN
IF ( NOT ISBLANK ( __prev ), 'powerbi114 view_finance'[Start Time] - __prev )

[Qotsa]

@CNENFRNL Thks for your help. I just needed to add an earlier date filter to your code to get it to do what I needed.

Thks again. Much appreciated.

Break =

var __prev =

MAXX(

TOPN(

1,

FILTER('powerbi114 view_finance',

'powerbi114 view_finance'[carer_id] = EARLIER('powerbi114 view_finance'[carer_id])

&& 'powerbi114 view_finance'[date] = EARLIER('powerbi114 view_finance'[date])

&& 'powerbi114 view_finance'[Sched_Finish] <= EARLIER('powerbi114 view_finance'[Sched_Start])

),

'powerbi114 view_finance'[Sched_Finish]

),

'powerbi114 view_finance'[Sched_Finish]

)

return

IF( NOT ISBLANK(__prev),'powerbi114 view_finance'[Sched_Start]- __prev)

[CNENFRNL · Accepted solution]

[Qotsa]

@CNENFRNL That works well in sample file but i think having other days and employees throws it off.

For every day I need to show it for each employee. For their first & last shift that day it should return blank.

sample pbix file link 

[HotChilli]

This will give you a column with the previous end time so you can get the difference with a subtraction:

Add a custom column (sub in previous step for #"Changed Type")

let a = [Sched_Start] in  List.Max(List.Select(#"Changed Type"[Sched_Finish], each _ <= a))

it will be a little more complex with different employee ids

[Qotsa]

@HotChilli TY. I need to have this in DAX rather than Query Editor.