Generate schedule to identify scheduled shift

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