Generate calendar with shift rotation

[Darell]

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!

[ppm1 · Accepted solution]

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

[Darell]

Thank you! No words how grateful I am.

[BrianConnelly]

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)