Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Time between shift finish time and next shift start time

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 

 

 

 

 

Need Help

6 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

       

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 )