Forum Discussion
Generate calendar with shift rotation
Hi, I am struggling to create a DAX formula to generate a calendar (datetime table) with four-day rotation.
Every shift is working 12 hours between 07:00 - 18:59, then a pause of 24 hours; again 12 working hours between 19:00 - 06:59, then a pause of 48 hours.
This is a pattern I want to extend for the whole 2023 year:
| 13.03.2023 | 07:00 | Shift 1 |
| 13.03.2023 | 19:00 | Shift 3 |
| 14.03.2023 | 07:00 | Shift 4 |
| 14.03.2023 | 19:00 | Shift 1 |
| 15.03.2023 | 07:00 | Shift 2 |
| 15.03.2023 | 19:00 | Shift 4 |
| 16.03.2023 | 07:00 | Shift 3 |
| 16.03.2023 | 19:00 | Shift 2 |
Because this message cannot exceed 20,000 characters, I deleted the rows with hours between 07:00 - 19:00 and 19:00 - 07:00, but I need them too in the generated table to create relashionship with other table.
Thank you!
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = List.Transform({0..(365*24*60)}, each #datetime(2023,1,1,9,0,0) + #duration(0,0,_,0)), ShiftTimesLists = List.Split(Source, 12 * 60), ShiftList = {1,3,4,1,2,4,3,2}, ShiftListRepeat = List.FirstN(List.Repeat(ShiftList, Number.RoundUp(List.Count(ShiftTimesLists)/8, 0)), List.Count(ShiftTimesLists)), ZipList = List.Zip({ShiftTimesLists, ShiftListRepeat}), TransformToRecords = List.Transform(ZipList, each [Time = _{0}, Shift = _{1}]), #"Converted to Table" = Table.FromList(TransformToRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Time", "Shift"}, {"Time", "Shift"}), #"Expanded Time" = Table.ExpandListColumn(#"Expanded Column1", "Time"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type datetime}, {"Shift", Int64.Type}}) in #"Changed Type"Pat
3 Replies
BrianConnellyResolver III
The better method would be using the Power Query Editor. Create a Date Table and add 4 columns for each shift. Once that is done, Unpivot those 4 columns which will give you a 4 shift rows for each date.
If using DAX, you can use Addcolumns to your date table and create four tables and then Unionize...
Four Shift Table = Var Shift1 = ADDCOLUMNS(CALENDARAUTO(12),"Shift",TIMEVALUE("07:00")) Var Shift2 = ADDCOLUMNS(CALENDARAUTO(12),"Shift",TIMEVALUE("08:00")) Var Shift3 = ADDCOLUMNS(CALENDARAUTO(12),"Shift",TIMEVALUE("09:00")) Var Shift4 = ADDCOLUMNS(CALENDARAUTO(12),"Shift",TIMEVALUE("10:00")) RETURN UNION(Shift1,Shift2,Shift3,Shift4)
ppm1Solution Sage
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = List.Transform({0..(365*24*60)}, each #datetime(2023,1,1,9,0,0) + #duration(0,0,_,0)), ShiftTimesLists = List.Split(Source, 12 * 60), ShiftList = {1,3,4,1,2,4,3,2}, ShiftListRepeat = List.FirstN(List.Repeat(ShiftList, Number.RoundUp(List.Count(ShiftTimesLists)/8, 0)), List.Count(ShiftTimesLists)), ZipList = List.Zip({ShiftTimesLists, ShiftListRepeat}), TransformToRecords = List.Transform(ZipList, each [Time = _{0}, Shift = _{1}]), #"Converted to Table" = Table.FromList(TransformToRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Time", "Shift"}, {"Time", "Shift"}), #"Expanded Time" = Table.ExpandListColumn(#"Expanded Column1", "Time"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type datetime}, {"Shift", Int64.Type}}) in #"Changed Type"Pat
Thank you! No words how grateful I am.