Forum Discussion

Mramono's avatar
Mramono
Icon for Helper III rankHelper III
1 year ago
Solved

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 anothe...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    '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.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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's avatar
Mramono
Icon for Helper III rankHelper III
11 months ago

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 well

 

But 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

  • Mramono's avatar
    Mramono
    Icon for Helper III rankHelper III
    11 months ago

    johnt75 Apologies, i managed to find what i was doing wrong. In fact i was busy adding it as a measure instead of calculated column. It's working. apologies for the trouble