Create Scheduled Work Roster in PowerBI

[kristyd43]

Hi All,

I am trying to create a work roster in Powerbi.

The roster has 4 crews; A, B, C and D.

There is a nightshift crew and a dayshift crew that rotates weekly (wednesdays first day on, wednesday last day off).

For example: A crew on dayshift for week 1, C crew on nightshift for week1, B and D crews are on days off. Then week 2, B crew=Dayshift, D Crew=nightshift and A and C are rostered off. Week 3, A crew rotates to Nightshift, C crew on dayshift, B and D off.

Here is the calendar data I am working with and what I need the output to be for crew rosters:

(06:00 = DayShift, 18:00=NightShift)

Date reference	WeekReference	Output
28/06/2023 6:00	06:00:00,2023-06-28	A
28/06/2023 18:00	18:00:00,2023-06-28	C
29/06/2023 6:00	06:00:00,2023-06-28	A
29/06/2023 18:00	18:00:00,2023-06-28	C
30/06/2023 6:00	06:00:00,2023-06-28	A
30/06/2023 18:00	18:00:00,2023-06-28	C
1/07/2023 6:00	06:00:00,2023-06-28	A
1/07/2023 18:00	18:00:00,2023-06-28	C
2/07/2023 6:00	06:00:00,2023-06-28	A
2/07/2023 18:00	18:00:00,2023-06-28	C
3/07/2023 6:00	06:00:00,2023-06-28	A
3/07/2023 18:00	18:00:00,2023-06-28	C
4/07/2023 6:00	06:00:00,2023-06-28	A
4/07/2023 18:00	18:00:00,2023-06-28	C
5/07/2023 6:00	06:00:00,2023-07-05	B
5/07/2023 18:00	18:00:00,2023-07-05	D
6/07/2023 6:00	06:00:00,2023-07-05	B
6/07/2023 18:00	18:00:00,2023-07-05	D
7/07/2023 6:00	06:00:00,2023-07-05	B
7/07/2023 18:00	18:00:00,2023-07-05	D
8/07/2023 6:00	06:00:00,2023-07-05	B
8/07/2023 18:00	18:00:00,2023-07-05	D
9/07/2023 6:00	06:00:00,2023-07-05	B
9/07/2023 18:00	18:00:00,2023-07-05	D
10/07/2023 6:00	06:00:00,2023-07-05	B
10/07/2023 18:00	18:00:00,2023-07-05	D
11/07/2023 6:00	06:00:00,2023-07-05	B
11/07/2023 18:00	18:00:00,2023-07-05	D
12/07/2023 6:00	06:00:00,2023-07-12	C
12/07/2023 18:00	18:00:00,2023-07-12	A
13/07/2023 6:00	06:00:00,2023-07-12	C
13/07/2023 18:00	18:00:00,2023-07-12	A
14/07/2023 6:00	06:00:00,2023-07-12	C
14/07/2023 18:00	18:00:00,2023-07-12	A
15/07/2023 6:00	06:00:00,2023-07-12	C
15/07/2023 18:00	18:00:00,2023-07-12	A
16/07/2023 6:00	06:00:00,2023-07-12	C
16/07/2023 18:00	18:00:00,2023-07-12	A
17/07/2023 6:00	06:00:00,2023-07-12	C
17/07/2023 18:00	18:00:00,2023-07-12	A
18/07/2023 6:00	06:00:00,2023-07-12	C
18/07/2023 18:00	18:00:00,2023-07-12	A
19/07/2023 6:00	06:00:00,2023-07-19	D
19/07/2023 18:00	18:00:00,2023-07-19	B
20/07/2023 6:00	06:00:00,2023-07-19	D
20/07/2023 18:00	18:00:00,2023-07-19	B
21/07/2023 6:00	06:00:00,2023-07-19	D
21/07/2023 18:00	18:00:00,2023-07-19	B
22/07/2023 6:00	06:00:00,2023-07-19	D
22/07/2023 18:00	18:00:00,2023-07-19	B
23/07/2023 6:00	06:00:00,2023-07-19	D
23/07/2023 18:00	18:00:00,2023-07-19	B
24/07/2023 6:00	06:00:00,2023-07-19	D
24/07/2023 18:00	18:00:00,2023-07-19	B
25/07/2023 6:00	06:00:00,2023-07-19	D
25/07/2023 18:00	18:00:00,2023-07-19	B
26/07/2023 6:00	06:00:00,2023-07-26	A
26/07/2023 18:00	18:00:00,2023-07-26	C
27/07/2023 6:00	06:00:00,2023-07-26	A
27/07/2023 18:00	18:00:00,2023-07-26	C

[Idrissshatila]

Hello @kristyd43 ,

check if this video will be a help in your case https://youtu.be/6JA6uy3ZRBA

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin