Forum Discussion
Assigning Rotating Shift Teams based on Date
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 |
'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.
18 Replies
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.
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)
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!
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.
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.
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.
...
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] ) )
johnt75 Thank you again for helping me with the problem. I have been using the solution you proposed creating a helper table to assign rotating shifts based on the date. I have applied this solution several times now with no issue. But for some reason today is giving me a DAX error and is not recognising the column/field that is obviously there. The first line of your DAX picks up the column but the second line does not and i cannot figure out why. I have been stuck on this for very long time now checking everything. Perhaps you can help me figure out where is this wrong now.
**********************************************************************************
Offset =VAR StartDate = CALCULATE( MIN( 'TeamsShiftRoster'[Production Date] ), REMOVEFILTERS() )VAR Result = DATEDIFF( StartDate, 'TeamsShiftRoster'[Production Date], DAY )RETURN Result******************************************************************************************************Here is woking wellBut now when i apply the same strategy that has been working so well before, is now giving me an error
I really not sure why is no longer working
Power Query (M) Solution
Go to Power Query Editor.
Select your table (with Date and Shift).
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
TeamResult
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 🙏.
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.
Can i see screenshot
Problem still not solved
Will appreciate some help
kind regards
mramono
Here is the Powerbi data file https://marampamines-my.sharepoint.com/:u:/p/mramonotsi/ERTFLCttYV5LntO_WsR4Ct8BQ-_N3hgF0Cw9S1j3JwWEiA?e=OgUX9d
