Assigning Rotating Shift Teams based on Date

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_content=profile&utm_medium=ios_app and https://medium.com/@nnooonani2015/my-journey-with-microsoft-fabric-why-i-recommend-joining-this-community-7c04e7bbe784)for more updates.

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