Generate schedule to identify scheduled shift

[nlynch1982]

Hi, I am struggling to create a DAX formula to generate a calendar (date table) with three-day rotation that flips workdays every 6 weeks to be used to identify which team(s) are scheduled to be working any given day. 

Example: Rotation B has Day1 and Night1 shifts working Wed-Fri for 6 weeks with Day2 working Sat, Mon & Tues and Night2 working Sun-Tues. After 6 weeks, the Day1 shift will also work Sat, Mon & Tues with Night1 also working Sun-Tues with Day2 and Night2 teams returning Wednesday completing the transition to Rotation A. Rotation A will run for 6 weeks before switching back t Rotation B.

This needs to calculate perpetually, starting 11/30/2022 and I am at a loss. 

Thank you for any assistance!

[OwenAuger]

Thanks @nlynch1982 !

Have been a bit busy and looking at this properly now.

I just wanted to check a minor point in the file you shared. There seems to be a slight error - possibly just a copy/paste error.

• There seems to be an extra row of shifts inserted on 28-Jan, which has pushed subsequent shifts down one row.
• Saturdays should just have a Day shift shouldn't they?
• On Sat 28-Jan-2023 there should be just Day2 right?

Putting that to one side, in order to help define the table, we could first define "week types" as follows:

Week Type	Wed-Fri	Sat-Tue
1-2	Day1/Night1	Day2/Night2
1-1	Day1/Night1	Day1/Night1
2-1	Day2/Night2	Day1/Night1

Then the schedule can be summarised as:

Rotation	Week #	Week Type
A	1	1-1
A	2	2-1
A	3	2-1
A	4	2-1
A	5	2-1
A	6	2-1
B	1	1-2
B	2	1-2
B	3	1-2
B	4	1-2
B	5	1-2
B	6	1-2

Does the above table look correct? I will get to the DAX shortly!

Regards

[nlynch1982]

Many apologies for the typo, I have corrected the sheet on the drive! 

Only Day shift will work Saturdays and only night shift will work on Sundays.

For clarity sake, I wanted to add a row 2-2 which has Day2 and Night2 for Wed-Fri and Sat-Tue.

1-1 and 2-2 are 'Transition Weeks' where 1-2 and 2-1 would be considered the 'typical schedule' for the rotation. 

Thank you again, very much @OwenAuger !

[OwenAuger · Accepted solution]

@nlynch1982 no problem!

I've come up with DAX code for a calculated table which I believe does what you want.

I've tried to parameterise things so that you can adjust as needed.

The basic method used is to

1. Set up some parameters (scalars and  tables) up-front
2. Create a calendar without the shift information (CalendarExtended)
3. Add the shift information using joins

Calendar =
-- ===========================================================
-- SECTION A: Parameters
-- ===========================================================

-- ----------------------------------------------------------
-- A1. MISCELLANEOUS PARAMETERS
-- ----------------------------------------------------------

-- Calendar bounds
VAR MinDate = dt"2022-11-23"
VAR MaxDate = dt"2023-02-28"

-- Number of weeks per full rotation
-- This must be equal to the number of rows of WeekTypes below
VAR NumWeeksFullRotation = 12

-- Days per week (should always be 7)
VAR DaysPerWeek = 7

-- Prefixes used for the Day/Night shifts when displayed in the final table
VAR DayPrefix = "Day"
VAR NightPrefix = "Night"

-- Weekday return types
-- Type 13 gives Wed = 1 to Tue = 7
-- Type 16 gives Sat = 1 to Fri = 7
VAR WeekdayType = 13
VAR WeekdayTypePresentation = 16

-- Reference date should be a date in the first week of Rotation A
VAR ReferenceDate = dt"2022-11-30"

-- ----------------------------------------------------------
-- A2. PARAMETER: Weektypes
-- ----------------------------------------------------------
-- WeekTypes gives each of the 12 weeks a zero-based index WeekIndex
-- and specifies the Rotation (A or B)
-- and the shift numbers in Block 1 & Block 2 of the week.
-- For example:
--   The row {  1, "A", 2, 1 }
--   means
--     Week 1 is Rotation A,
--     and assigns Day/Night Shift 2 to the 1st Block of the week
--     and assigns  Day/Night Shift 1 to the 2nd Block of the week.
--   In other words, Week 1 is a 2-1 week in Block A.
VAR WeekTypes =
    DATATABLE (
        "WeekIndex", INTEGER,
        "Rotation", STRING,
        "Block1", INTEGER,
        "Block2", INTEGER,
        {
            { 0, "A", 1, 1 },
            { 1, "A", 2, 1 },
            { 2, "A", 2, 1 },
            { 3, "A", 2, 1 },
            { 4, "A", 2, 1 },
            { 5, "A", 2, 1 },
            { 6, "B", 1, 2 },
            { 7, "B", 1, 2 },
            { 8, "B", 1, 2 },
            { 9, "B", 1, 2 },
            { 10, "B", 1, 2 },
            { 11, "B", 1, 2 }
        }
    )

-- ----------------------------------------------------------
-- A3. PARAMETER: ShiftPattern
-- ----------------------------------------------------------
-- ShiftPattern specifices when the Block 1 & Block 2 shifts appear on weekdays.
-- Note: Weekdays numbered Wed = 1 to Tue = 7
-- Currently, Block 1 shifts appear on Wed-Fri & Block 2 shifts appear on Sat-Tue.
-- Blanks indicate no shift is working in that slot.
-- For example:
--   The row { 1, 1, 1 }
--   means on Weekday 1 (Wed)
--   Day shift is the shift assigned to Block 1
--   Night shift is the shift assigned to Block 1
VAR ShiftPattern =
    DATATABLE (
        "Weekday", INTEGER,
        "Day", INTEGER,
        "Night", INTEGER,
        {
            { 1, 1, 1 },
            { 2, 1, 1 },
            { 3, 1, 1 },
            { 4, 2, BLANK () },
            { 5, BLANK (), 2 },
            { 6, 2, 2 },
            { 7, 2, 2 }
        }
    )

-- ===========================================================
-- SECTION B: CALCULATIONS
-- ===========================================================

-- Number of days per full rotation (A & B).Currently 12 * 7 = 84
VAR NumDaysFullRotation = NumWeeksFullRotation * DaysPerWeek

-- Reference date adjusted to Wednesday to ensure it is the start of a week
-- If  different WeekdayType is used, it is assumed to be numbered 1-7
VAR ReferenceDateAdj =
    ReferenceDate - WEEKDAY ( ReferenceDate, WeekdayType ) + 1
VAR CalendarBase =
    CALENDAR ( MinDate, MaxDate )
VAR CalendarExtended =
    GENERATE (
        CalendarBase,
        VAR d = [Date]
        VAR CurrentWeekStart =
            d - WEEKDAY ( d, WeekdayType ) + 1
        VAR WeekIndex =
            QUOTIENT ( MOD ( d - ReferenceDateAdj, NumDaysFullRotation ), DaysPerWeek )
        VAR WeekDay =
            WEEKDAY ( d, WeekdayType )
        VAR WeekDayPresentation =
            WEEKDAY ( d, WeekDayTypePresentation )
        RETURN
            ROW (
                "WeekIndex", WeekIndex,
                "Weekday", WeekDay,
                "Weekday Presentation", WeekdayPresentation
            )
    )
VAR CalendarJoinWeekTypes =
    NATURALLEFTOUTERJOIN ( CalendarExtended, WeekTypes )
VAR CalendarJoinShiftPattern =
    NATURALLEFTOUTERJOIN ( CalendarJoinWeekTypes, ShiftPattern )
VAR CalendarFinal =
    SELECTCOLUMNS (
        CalendarJoinShiftPattern,
        "Date", [Date],
        "Weekday", [Weekday Presentation],
        "Rotation", [Rotation],
        "Day",
            IF (
                NOT ISBLANK ( [Day] ),
                DayPrefix & SWITCH ( [Day], 1, [Block1], 2, [Block2] )
            ),
        "Night",
            IF (
                NOT ISBLANK ( [Night] ),
                NightPrefix & SWITCH ( [Night], 1, [Block1], 2, [Block2] )
            )
    )
RETURN
    CalendarFinal

Please post back if needed. Can also get back to you on a Power Query method later 🙂

Regards

[nlynch1982]

@OwenAuger 

Thank you so much!! This is exactly what I need! Very, very much appreciative for you!

[OwenAuger]

Hi @nlynch1982 

This should be possible.

1. Just to clarify, could you post a calendar showing a complete cycle of Rotation A & Rotation B (which I assume is 12 weeks in total)? If you could share an Excel file by OneDrive or Google Drive that would be great.
2. I take it that Rotation A always looks the same, as does Rotation B?

Also, do you prefer to create this table in DAX or would you be open to Power Query as well?

Regards

[nlynch1982]

Hi @OwenAuger !

1. Here is the link to excel file in Google Drive - yes, a full rotation is 12 weeks, 

2. For this specific case, DAX is preferred - but knowing how to do this in Power Query would be very helpful!

Thank you!!