Search code examples
variablesdaxpowerpivot

Alternative to network days for rotating shift patterns - DAX PowerPivot

I recently asked a question and had some brilliant help (Sam Nseir!).

I am working in PowerPivot and have successfully (with the aforementioned help) managed to assign the first date of an occurrence of sickness to each date in the occurrence (through the use of two calculated columns).

It also accounts for Fridays and Mondays and treats them as consecutive days (in the absence of the NETWORKDAYS function which isn't available).

However, I have now realised that we have rotating shift patterns for drivers. So, I need to amend the code to account for a variety of different patterns. I have a field which says which shift each of these workers is on (It's not all workers, only drivers so I still need to be account for the standard mon-fri) and I have tables (tabs) for each shift type (each different pattern) which lists all of the dates (row by row) between 2023 - 2026 that are worked on this shift pattern. So I am hoping to find a way to reference these tables to say which dates should be considered consecutive - it's extra tricky because they are rotating shift patterns so move by one day each week.

Example of a shift pattern (there is a separate table for each shift type (6 in total):

Calendar Event Start Date Day Weekday
Shift 6 18/02/2023 7 Sat
Shift 6 21/02/2023 3 Tues
Shift 6 22/02/2023 4 Weds
Shift 6 23/02/2023 5 Thurs
Shift 6 24/02/2023 6 Fri
Shift 6 27/02/2023 2 Mon
Shift 6 28/02/2023 3 Tues
Shift 6 01/03/2023 4 Weds
Shift 6 02/03/2023 5 Thurs
Shift 6 05/03/2023 1 Sun
Shift 6 06/03/2023 2 Mon
Shift 6 07/03/2023 3 Tues
Shift 6 08/03/2023 4 Weds
Shift 6 11/03/2023 7 Sat
Shift 6 12/03/2023 1 Sun
Shift 6 13/03/2023 2 Mon
Shift 6 14/03/2023 3 Tues
Shift 6 17/03/2023 6 Fri
Shift 6 18/03/2023 7 Sat
Shift 6 19/03/2023 1 Sun
Shift 6 20/03/2023 2 Mon
Shift 6 23/03/2023 5 Thurs
Shift 6 24/03/2023 6 Fri
Shift 6 25/03/2023 7 Sat
Shift 6 26/03/2023 1 Sun
Shift 6 29/03/2023 4 Weds
Shift 6 30/03/2023 5 Thurs
Shift 6 31/03/2023 6 Fri
Shift 6 01/04/2023 7 Sat
Shift 6 04/04/2023 3 Tues
Shift 6 05/04/2023 4 Weds
Shift 6 06/04/2023 5 Thurs
Shift 6 07/04/2023 6 Fri
Shift 6 10/04/2023 2 Mon
Shift 6 11/04/2023 3 Tues
Shift 6 12/04/2023 4 Weds

The current logic I have (worked out for me by some one else - not my own knowledge!):

Calculated Column 1 code -(FirstDateOccurrence:(https://i.sstatic.net/9l3Yq.png)

Calculated Column 2 code - Absence Start Date: (https://i.sstatic.net/MbJnO.png)

An example below of what these columns return:

Employee ID Time Off Date Approved Time Off Reason FirstDateOccurrence Absence Start Date
123 21/02/2023 1 Anxiety/Stress 20/02/2023
123 22/02/2023 1 Anxiety/Stress 20/02/2023
123 23/02/2023 1 Anxiety/Stress 20/02/2023
123 27/02/2023 1 Anxiety/Stress 27/02/2023 27/02/2023
123 28/02/2023 1 Anxiety/Stress 27/02/2023
123 01/03/2023 1 Anxiety/Stress 27/02/2023
123 02/03/2023 1 Anxiety/Stress 27/02/2023
123 06/03/2023 1 Anxiety/Stress 06/03/2023 06/03/2023
123 07/03/2023 1 Anxiety/Stress 06/03/2023
123 08/03/2023 1 Anxiety/Stress 06/03/2023
123 09/03/2023 1 Anxiety/Stress 06/03/2023
123 13/03/2023 1 Anxiety/Stress 13/03/2023 13/03/2023
123 14/03/2023 1 Anxiety/Stress 13/03/2023
123 15/03/2023 1 Anxiety/Stress 13/03/2023
123 16/03/2023 1 Anxiety/Stress 13/03/2023
123 20/03/2023 1 Anxiety/Stress 20/03/2023 20/03/2023
123 21/03/2023 1 Anxiety/Stress 20/03/2023
123 22/03/2023 1 Anxiety/Stress 20/03/2023
123 23/03/2023 1 Anxiety/Stress 20/03/2023
123 27/03/2023 1 Anxiety/Stress 27/03/2023 27/03/2023
123 28/03/2023 1 Anxiety/Stress 27/03/2023
123 29/03/2023 1 Anxiety/Stress 27/03/2023
123 30/03/2023 1 Anxiety/Stress 27/03/2023
123 03/04/2023 1 Anxiety/Stress 03/04/2023 03/04/2023
123 04/04/2023 1 Anxiety/Stress 03/04/2023
123 05/04/2023 1 Anxiety/Stress 03/04/2023
123 06/04/2023 1 Anxiety/Stress 03/04/2023
123 10/04/2023 1 Anxiety/Stress 10/04/2023 10/04/2023
123 11/04/2023 1 Anxiety/Stress 10/04/2023
123 12/04/2023 1 Anxiety/Stress 10/04/2023

So essentially, I am hoping to achieve the above but also accounting for the rotating shift patterns where an occurrence spans the 'off' days and into the next shift period - so then it is all counted as the same sickness occurrence - like when an absence spans over a weekend for a standard working week.

Any help hugely appreciated - I hate sickness data haha.

Solution

  • You can try updating the FirstDateOccurrence calculated column with:

    =
  var thisId = [Employee ID]
  var thisReason = [Time Off Reason]
  var thisDate = [Time Off Date]
  var thisShift = [Shift]
  var prevRow = TOPN(1, FILTER('Table1', [Employee ID] = thisId && [Time Off Reason] = thisReason && [Time Off Date] < thisDate), [Time Off Date], DESC)
  var prevDate = CALCULATE(MAX([Time Off Date]), prevRow)
  
  var shiftDiff = SWITCH(thisShift,
    1, COUNTROWS(FILTER('Shift 1', 'Shift 1'[Start Date] >= prevDate && 'Shift 1'[Start Date] <= thisDate)),
    2, COUNTROWS(FILTER('Shift 2', 'Shift 2'[Start Date] >= prevDate && 'Shift 2'[Start Date] <= thisDate)),
    3, COUNTROWS(FILTER('Shift 3', 'Shift 3'[Start Date] >= prevDate && 'Shift 3'[Start Date] <= thisDate)),
    4, COUNTROWS(FILTER('Shift 4', 'Shift 4'[Start Date] >= prevDate && 'Shift 4'[Start Date] <= thisDate)),
    5, COUNTROWS(FILTER('Shift 5', 'Shift 5'[Start Date] >= prevDate && 'Shift 5'[Start Date] <= thisDate)),
    6, COUNTROWS(FILTER('Shift 6', 'Shift 6'[Start Date] >= prevDate && 'Shift 6'[Start Date] <= thisDate))
  )
  
  return SWITCH(TRUE(),
    ISBLANK(prevDate), thisDate,
    shiftDiff = 2, BLANK(),
    ISBLANK(thisShift) && WEEKDAY(prevDate, 2) = 5 && DATEDIFF(prevDate, thisDate, DAY) < 4, BLANK(),
    DATEDIFF(prevDate, thisDate, DAY) <> 1, thisDate
  )