Forum Discussion
Helper IIIAssigning 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.
Super UserYou can create it by using a helper table which stores the shift rotations along with the day offset. See attached PBIX for the solution.
MramonoHi 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.
- Mramono
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