Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Darell
New Member

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.202307:00Shift 1
13.03.202319:00Shift 3
14.03.202307:00Shift 4
14.03.202319:00Shift 1
15.03.202307:00Shift 2
15.03.202319:00Shift 4
16.03.202307:00Shift 3
16.03.202319:00Shift 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!

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution 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

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution 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

Microsoft Employee

Thank you! No words how grateful I am.

BrianConnelly
Resolver III
Resolver 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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors