Assigning Rotating Shift Teams based on Date

[Mramono]

I have 4 teams (Team A, B, C, D) that are rotating shifts based on the date. my fact table has a column that specify the shift (MorningShift, AfternoonShift, NightShift and OFF). I want to get another colum that will assign each shift to a team based on the date. The table below illustrate how the teams rotate based on the date to different shifts. Each Team stays in the same shift for 3 days and then move to the next shift. E.g. On the 27/08/2025 to 29/08/2025: Team C is doing MorningShift, Team D is doing AfternoonShift, Team A is doing NightShift and Team B is OFF. The table below illustrate the rotation based on date. I need to add a column that will look at the date assign each shift according to the relevent Team.

	Shift
Date	Morning	Afternoon	Night	OFF
21/08/2025	A	B	C	D
22/08/2025	A	B	C	D
23/08/2025	A	B	C	D
24/08/2025	D	A	B	C
25/08/2025	D	A	B	C
26/08/2025	D	A	B	C
27/08/2025	C	D	A	B
28/08/2025	C	D	A	B
29/08/2025	C	D	A	B
30/08/2025	B	C	D	A
31/08/2025	B	C	D	A
01/09/2025	B	C	D	A
02/09/2025	A	B	C	D
03/09/2025	A	B	C	D
04/09/2025	A	B	C	D
05/09/2025	D	A	B	C
06/09/2025	D	A	B	C
07/09/2025	D	A	B	C
08/09/2025	C	D	A	B
09/09/2025	C	D	A	B
10/09/2025	C	D	A	B

[Nabha-Ahmed]

Power Query (M) Solution

1. Go to Power Query Editor.

2. Select your table (with Date and Shift).

3. Add a Custom Column with this M code: 

let
// Calculate DayIndex starting from the first date
DayIndex = Duration.Days([Date] - List.Min(#"PreviousStep"[Date])),

// Every 3 days = new block
Block = Number.IntegerDivide(DayIndex, 3),

// Rotation 0,1,2,3 → then repeat
Rotation = Number.Mod(Block, 4),

// Assign Team based on Shift + Rotation
Team =
if [Shift] = "MorningShift" and Rotation = 0 then "A"
else if [Shift] = "AfternoonShift" and Rotation = 0 then "B"
else if [Shift] = "NightShift" and Rotation = 0 then "C"
else if [Shift] = "OFF" and Rotation = 0 then "D"

else if [Shift] = "MorningShift" and Rotation = 1 then "D"
else if [Shift] = "AfternoonShift" and Rotation = 1 then "A"
else if [Shift] = "NightShift" and Rotation = 1 then "B"
else if [Shift] = "OFF" and Rotation = 1 then "C"

else if [Shift] = "MorningShift" and Rotation = 2 then "C"
else if [Shift] = "AfternoonShift" and Rotation = 2 then "D"
else if [Shift] = "NightShift" and Rotation = 2 then "A"
else if [Shift] = "OFF" and Rotation = 2 then "B"

else if [Shift] = "MorningShift" and Rotation = 3 then "B"
else if [Shift] = "AfternoonShift" and Rotation = 3 then "C"
else if [Shift] = "NightShift" and Rotation = 3 then "D"
else if [Shift] = "OFF" and Rotation = 3 then "A"
else null
in
Team

Result

This will add a Team column in Power Query that assigns each row’s shift to the right team automatically based on your 3-day rotation rule.

If this solution worked for you, please mark it as Solved ✅ so it can help more people find it quickly.

Feel free to connect with me on (https://www.linkedin.com/in/nabha-ahmed-166491221?utm_source=share&utm_campaign=share_via&utm_conten... and https://medium.com/@nnooonani2015/my-journey-with-microsoft-fabric-why-i-recommend-joining-this-comm...)for more updates.

And if you found this helpful, I’d really appreciate a Kudo 🙏.

[Mramono]

Hi @Nabha-Ahmed i tried applied the solution you provided for M-code but i keep getting error feedback. not sure where i am messing things up.

[Mramono]

@Nabha-Ahmed here is the screen shot

[Nabha-Ahmed]

Can i see screenshot 

[johnt75]

You can create it by using a helper table which stores the shift rotations along with the day offset. See attached PBIX for the solution.

[Mramono]

Hi @johnt75 thank you once again for the proposed solution you send me. i have been looking at it since yesterdsay trying to figure out how i can use it to solve my problem. Basically I would like to add additional ONE calculated column on my "D_Prod" table which should automatically assign a team to a particular SHIFT based on the date and time. E.g.

On 1st april A-Team is assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it will be OFF. On 2nd april A-Team is still assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it will be OFF. On 3rd april A-Team is still assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it will be OFF.

Shift change

On the 4th April now D-Team (that was off) is now assigned MorningShift, A-Team is assigned AfternoonShif, B-Team is assigned NightShift, and C-Team does not appear on the column because it is now OFF. On the 5th April D-Team is still assigned MorningShift, A-Team is assigned AfternoonShif, B-Team is assigned NightShift, and C-Team does not appear on the column because it now will be OFF. On the 6th April D-Team is still assigned MorningShift, A-Team is assigned AfternoonShif, B-Team is assigned NightShift, and C-Team does not appear on the column because it is OFF. 

Shift Change

On the 7th April now C-Team (that was off) is now assigned MorningShift, D-Team is assigned AfternoonShif, A-Team is assigned NightShift, and B-Team does not appear on the column because it is now OFF. On the 8th April now C-Team is still assigned MorningShift, D-Team is assigned AfternoonShif, A-Team is assigned NightShift, and B-Team does not appear on the column because it is now OFF.  On the 9th April now C-Team is still assigned MorningShift, D-Team is assigned AfternoonShif, A-Team is assigned NightShift, and B-Team does not appear on the column because it is now OFF. 

Shift Change

On the 10th April now B-Team (that was off) is now assigned MorningShift, C-Team is assigned AfternoonShif, D-Team is assigned NightShift, and A-Team does not appear on the column because it is now OFF. On the 11th April now B-Team is still assigned MorningShift, C-Team is assigned AfternoonShif, D-Team is assigned NightShift, and A-Team does not appear on the column because it is now OFF.  On the 12th April now B-Team is still assigned MorningShift, C-Team is assigned AfternoonShif, D-Team is assigned NightShift, and A-Team does not appear on the column because it is now OFF. 

Shift Change (cycle repeat)

On the 13th April now A-Team (that was off) is now assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it is now OFF. On the 14th April now A-Team is still assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it is now OFF.  On the 15th April now A-Team is still assigned MorningShift, B-Team is assigned AfternoonShif, C-Team is assigned NightShift, and D-Team does not appear on the column because it is now OFF. 

...

[johnt75]

You can add a calculated column in D_Prod like

Team = SWITCH(
    D_Prod[ShiftPeriod],
    "AfternoonShift", LOOKUPVALUE( 'Dates with Shifts'[Afternoon], 'Dates with Shifts'[Date], D_Prod[Production Date] ),
    "MorningShift", LOOKUPVALUE( 'Dates with Shifts'[Morning], 'Dates with Shifts'[Date], D_Prod[Production Date] ),
    "NightShift", LOOKUPVALUE( 'Dates with Shifts'[Night], 'Dates with Shifts'[Date], D_Prod[Production Date] )
)

[Mramono]

Wonderful @johnt75 this is almost perfect. Can we do the same think without using the helper table. I understand "Base Table"  and "Dates with **bleep**fs" table. the table circled below complecates things for me. Or do i need to have this table generated for the whole year or is there a work around for it.

[johnt75 · Accepted solution]

'Table' is just there as a means of entering the data. You could instead manually enter 'Base Table' which has the shift and offset info, and then Dates With Shifts would generate itself from that.

You can also hide all these tables in report view so that they're not cluttering everything up.

[Mramono]

Thank you @johnt75 it worked perfectly

[Mramono]

Problem still not solved

Will appreciate some help

kind regards

mramono

[Mramono]

Here is the Powerbi data file https://marampamines-my.sharepoint.com/:u:/p/mramonotsi/ERTFLCttYV5LntO_WsR4Ct8BQ-_N3hgF0Cw9S1j3JwWE... 

[v-hashadapu]

Hi @Mramono , Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

[Mramono]

@v-hashadapu thank you for the proposal, unfortunately I can't figure out how to make the solution work for my data. Let me attach the link to my data hopefully you can see if you can further assist. I think the "OFF" row is the problematic one, my data is arranged such that only 3 shifts (Morning, Afternoon & Night) appear in the shift column. The team that is off is silent on the data until it comes back.

https://marampamines-my.sharepoint.com/:u:/p/mramonotsi/ERTFLCttYV5LntO_WsR4Ct8BQ-_N3hgF0Cw9S1j3JwWE... 

[Shahid12523]

Create a calculated column in DAX that uses the date to determine the team for each shift. The rotation follows a 12-day cycle, with each team staying in a shift for 3 days. Use MOD(DATEDIFF(...), 12) to find the cycle position, then assign teams using SWITCH(TRUE(), ...) for each shift.

[Mramono]

@Shahid12523 thank you for your quick response on this matter, i am new on PowerBI and my Dax is very basic. would you mind to write me a dax script. I attach the powerbi file link below if that will make it easier for you. We can start on 1 apr 2025 with Team A (MorningShift), Team B (AfternoonShift), Team C (NightShift) and Team D (OFF)

https://marampamines-my.sharepoint.com/:u:/p/mramonotsi/ERTFLCttYV5LntO_WsR4Ct8BQ-_N3hgF0Cw9S1j3JwWE...