topic Re: Generate schedule to identify scheduled shift in DAX Commands and Tips

Generate schedule to identify scheduled shift

nlynch1982 — Tue, 08 Aug 2023 12:05:32 GMT

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!

Re: Generate schedule to identify scheduled shift

OwenAuger — Sun, 13 Aug 2023 08:48:28 GMT

Hi nlynch1982

This should be possible.

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.
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

Re: Generate schedule to identify scheduled shift

nlynch1982 — Mon, 14 Aug 2023 11:09:13 GMT

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!!

Re: Generate schedule to identify scheduled shift

OwenAuger — Wed, 16 Aug 2023 12:04:10 GMT

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

Re: Generate schedule to identify scheduled shift

nlynch1982 — Wed, 16 Aug 2023 12:36:02 GMT

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 !

Re: Generate schedule to identify scheduled shift

OwenAuger — Fri, 18 Aug 2023 00:40:49 GMT

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

Set up some parameters (scalars and tables) up-front
Create a calendar without the shift information (CalendarExtended)
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

Re: Generate schedule to identify scheduled shift

nlynch1982 — Fri, 18 Aug 2023 11:55:39 GMT

OwenAuger

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