Connecting production teams to shifts

[Anonymous]

I work at a plant where they operate in 3 shifts :

Day: 7:00-15:00

Evening: 15:00-23:00

Night: 23:00-7:00

I have a shiftname column in my table.

I need to connect the shifts with 3 production teams:

This week(16):

Day: Blue team

Evening: Red team

Night: Green team

Next week(17): 

Day: Red team

Evening: Green team

Night: Blue team

You get the point, they cycle each 3 weeks 1 shift ealier.

The teams need to be connect to the shifts and cycle every week. But the hard part is that the cycle of teams don't reset at the start of a new year, it need to be continuous. So every team has 16,67 times a day shift in a year.

Can somebody help me with connecting the shifts to a new team column where the teams are connected the way I described above? Thanks in advance.

[Anonymous]

Thanks a lot, now I need to make a slicer where you can select the teams but I will figure that out! And setup the teams on the rigt week.

[Anonymous]

What's you e-mail? I will wetransfer. 

[johnt75 · Accepted solution]

Even though the week commencing column was set to a date type it was still storing it as a datetime and keeping the time part, so there were multiple rows being returned for the same date.

Change the definition of 'bi v_shift'[W/C] to

W / C = DATEVALUE(
	[dateTimeNearestHour] - WEEKDAY( [dateTimeNearestHour], 2 )
)

[johnt75]

Private message me

[johnt75]

You might want to rethink your model, as with that structure you will struggle with any time intelligence functions like year to date or comparing a period with the same period last year. For those functions to work they need a properly formatted date table which has one row per day for every day in the years you are concerned about. In your screenshot you have multiple entries for the same day, which is not ideal. There are plenty of articles and videos about setting up a date table.

You could add a W / C column to your existing table with

W / C =
[dateTimeNearestHour] - WEEKDAY ( [dateTimeNearestHour], 2 )

Make sure to set the format to date so that it removes the time part.

[Anonymous]

I know that the layout isn't optimal, the sql is setup by an external and is connected to an app. I need to reformat everything soon but for now I will hold on to this format because the dashboards work now.

Did it, got this now. Only the current W/C is missing.

 

[johnt75]

that should be 'bi shift'[w/c]

[Anonymous]

Getting real close now!😎  Almost fixed one of my biggest powerBI problems.

The only problem is that the teams are not all Green at day this week and all Red in the evening. Is there a solution to this? 

Mapping table:

W/C shift table:

  

[johnt75]

the w/c column needs to be a date, not a datetime. That way there will only be 1 value for a given week.

[Anonymous]

That makes no sense, still the same as the picture, do I need to change the dateTimeNearestHour in the quarry to date? That's where the W/C is based on.

[johnt75]

No, the dateTimeNearestHour doesn't need changing, just the week commencing column. All of the entries which cover a given week should have the same week commencing value, with just a date and no time attached to it.

[Anonymous]

Like this, right? Still not working.

[johnt75]

What does the team table look like ?

[Anonymous]

 

[johnt75]

That looks fine. Is the week commencing column on the shift table also a date, or is that a datetime? It needs to be a date.

[Anonymous]

It's a date.

[johnt75]

Can you create a PBIX that you can share? It only needs the shift table, the team table and the calculated weekly shifts table, so there shouldn't be any confidential info. You won't be able to attach a file but you could share it through OneDrive or Google drive, or a file transfer service.

[Adamboer]

Sure, I can help with that. Here's a possible solution for connecting the shifts to the corresponding production teams based on your description:

1. Create a table with the production teams and their corresponding colors:

Production Team Color

Blue	Blue
Red	Red
Green	Green

1. Create a calculated column in your existing table with the following formula:
   Team =
   VAR ShiftStart = TIME(HOUR([ShiftName]),0,0)
   VAR ShiftEnd = ShiftStart + TIME(8,0,0) // assuming 8-hour shifts
   VAR ShiftDateTime = DATEADD([Date],ShiftStart) // assuming the date is in a separate column
   VAR ShiftNumber = INT(([ShiftDateTime] - DATE(2023,1,1))/7) + 1 // assuming the year starts on January 1st and the first week is week 1
   VAR CycleNumber = MOD(ShiftNumber - 1,3) + 1
   VAR TeamName =
   SWITCH(CycleNumber,
   1, "Blue",
   2, "Red",
   

   This formula calculates the start and end times of the shift based on the hour of the ShiftName column. It then converts the date and time to a single datetime value, calculates the week number based on the number of days since January 1st of the current year, and calculates the cycle number as the remainder of the week number divided by 3. Finally, it uses a SWITCH statement to assign the appropriate team name based on the cycle number.

   1. Use the new Team column in your Pie chart to show the production teams by department sales.

   Hope this helps! Let me know if you have any questions.

   
   
   
   

[Anonymous]

Ain't working at all. I think that making a 2nd table next to the index is the solution. See johnt reaction. If I get the measure right it should be working properly. 

[Anonymous]

Thanks, I added the base(index) table and made a 2nd table. As a beginner I'm not the best in creating startdates or week commencing dates. This are some of the dates in my shift table, how can I convert this to the WC dates? Or maybe someone knows how to make the correct measure for me. That would be very helpfull because I only need to set this up once. Thanks in advance!

Calculated table:

 

Shift table: