Forum Discussion
Assigning Rotating Shift Teams based on Date
'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.
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] )
)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.
'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.