topic Re: Add crew details to date table in DAX Commands and Tips

Add crew details to date table

Jeron — Tue, 19 Jan 2021 03:27:59 GMT

Hi All, I need to add the crew column to the date table with the crew details (A,B,C,D) using DAX. We have totally 4 crews. If A & B crews works this week, C & D will be working in the next week. Also each crew alternate their shift time in their next turn. Example if A crew is on the day shift this week then they will come in the night shift in their next work week. Crew change happens on every Thursday.

See attached image for how i want the crew the column to be. Hope this explains the problem.Thanks for your help!

Re: Add crew details to date table

AlB — Tue, 19 Jan 2021 13:22:38 GMT

Hi Jeron

Do you want to share the table in text-tabular format, so that its contents can be copied?

What is the starting date to assig A,B and move on from?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Add crew details to date table

Jeron — Wed, 20 Jan 2021 00:41:48 GMT

Hi, Please see below. Let me know if you are able copy this.

On the date range, I need to populate the crew details for the last two years. But if i can get the solution for this sample date range, I should be able to use same technique for the larger date range.

Regarding the starting date for A or B, 03/12/2020 can be used as start date as shwon in the sample data here.

Thanks for your help.

Date	ShiftStartTime	ShiftEndTime	Shift	Crew
03/12/2020	12/03/2020 7:00	12/03/2020 18:59	DAY	A
03/12/2020	12/03/2020 19:00	12/04/2020 6:59	NIGHT	B
04/12/2020	12/04/2020 7:00	12/04/2020 18:59	DAY	A
04/12/2020	12/04/2020 19:00	12/05/2020 6:59	NIGHT	B
05/12/2020	12/05/2020 7:00	12/05/2020 18:59	DAY	A
05/12/2020	12/05/2020 19:00	12/06/2020 6:59	NIGHT	B
06/12/2020	12/06/2020 7:00	12/06/2020 18:59	DAY	A
06/12/2020	12/06/2020 19:00	12/07/2020 6:59	NIGHT	B
07/12/2020	12/07/2020 7:00	12/07/2020 18:59	DAY	A
07/12/2020	12/07/2020 19:00	12/08/2020 6:59	NIGHT	B
08/12/2020	12/08/2020 7:00	12/08/2020 18:59	DAY	A
08/12/2020	12/08/2020 19:00	12/09/2020 6:59	NIGHT	B
09/12/2020	12/09/2020 7:00	12/09/2020 18:59	DAY	A
09/12/2020	12/09/2020 19:00	12/10/2020 6:59	NIGHT	B
10/12/2020	12/10/2020 7:00	12/10/2020 18:59	DAY	C
10/12/2020	12/10/2020 19:00	12/11/2020 6:59	NIGHT	D
11/12/2020	12/11/2020 7:00	12/11/2020 18:59	DAY	C
11/12/2020	12/11/2020 19:00	12/12/2020 6:59	NIGHT	D
12/12/2020	12/12/2020 7:00	12/12/2020 18:59	DAY	C
12/12/2020	12/12/2020 19:00	12/13/2020 6:59	NIGHT	D
13/12/2020	12/13/2020 7:00	12/13/2020 18:59	DAY	C
13/12/2020	12/13/2020 19:00	12/14/2020 6:59	NIGHT	D
14/12/2020	12/14/2020 7:00	12/14/2020 18:59	DAY	C
14/12/2020	12/14/2020 19:00	12/15/2020 6:59	NIGHT	D
15/12/2020	12/15/2020 7:00	12/15/2020 18:59	DAY	C
15/12/2020	12/15/2020 19:00	12/16/2020 6:59	NIGHT	D
16/12/2020	12/16/2020 7:00	12/16/2020 18:59	DAY	C
16/12/2020	12/16/2020 19:00	12/17/2020 6:59	NIGHT	D
17/12/2020	12/17/2020 7:00	12/17/2020 18:59	DAY	B
17/12/2020	12/17/2020 19:00	12/18/2020 6:59	NIGHT	A
18/12/2020	12/17/2020 7:00	12/17/2020 18:59	DAY	B
18/12/2020	12/18/2020 19:00	12/19/2020 6:59	NIGHT	A
19/12/2020	12/19/2020 7:00	12/19/2020 18:59	DAY	B
19/12/2020	12/19/2020 19:00	12/20/2020 6:59	NIGHT	A
20/12/2020	12/20/2020 7:00	12/20/2020 18:59	DAY	B
20/12/2020	12/20/2020 19:00	12/21/2020 6:59	NIGHT	A
21/12/2020	12/21/2020 7:00	12/21/2020 18:59	DAY	B
21/12/2020	12/21/2020 19:00	12/22/2020 6:59	NIGHT	A
22/12/2020	12/22/2020 7:00	12/22/2020 18:59	DAY	B
22/12/2020	12/22/2020 19:00	12/23/2020 6:59	NIGHT	A
23/12/2020	12/23/2020 7:00	12/23/2020 18:59	DAY	B
23/12/2020	12/23/2020 19:00	12/24/2020 6:59	NIGHT	A
24/12/2020	12/24/2020 7:00	12/24/2020 18:59	DAY	D
24/12/2020	12/24/2020 19:00	12/25/2020 6:59	NIGHT	C
25/12/2020	12/25/2020 7:00	12/25/2020 18:59	DAY	D
25/12/2020	12/25/2020 19:00	12/26/2020 6:59	NIGHT	C
26/12/2020	12/26/2020 7:00	12/26/2020 18:59	DAY	D
26/12/2020	12/26/2020 19:00	12/27/2020 6:59	NIGHT	C
27/12/2020	12/27/2020 7:00	12/27/2020 18:59	DAY	D
27/12/2020	12/27/2020 19:00	12/28/2020 6:59	NIGHT	C
28/12/2020	12/28/2020 7:00	12/28/2020 18:59	DAY	D
28/12/2020	12/28/2020 19:00	12/29/2020 6:59	NIGHT	C
29/12/2020	12/29/2020 7:00	12/29/2020 18:59	DAY	D
29/12/2020	12/29/2020 19:00	12/30/2020 6:59	NIGHT	C
30/12/2020	12/30/2020 7:00	12/30/2020 18:59	DAY	D
30/12/2020	12/30/2020 19:00	12/31/2020 6:59	NIGHT	C

Re: Add crew details to date table

AlB — Wed, 20 Jan 2021 09:15:28 GMT

Jeron

Crew = VAR index_ = VAR currentDateTime_ = Table1[ShiftStartTime] RETURN CALCULATE ( COUNT ( Table1[ShiftStartTime] ), Table1[ShiftStartTime] <= currentDateTime_, ALL ( Table1 ) ) - 1 VAR aux_ = MOD ( QUOTIENT ( index_, 14 ), 4 ) VAR isEven_ = ISEVEN ( index_ ) RETURN SWITCH ( aux_, 0, IF ( isEven_, "A", "B" ), 1, IF ( isEven_, "C", "D" ), 2, IF ( isEven_, "B", "A" ), 3, IF ( isEven_, "D", "C" ) )

It can also be done directly in Power Query (probably better)

This assumes the dates for the shift are well-constructed. Watch out, in your sample table there is a repeated value, ShiftStartTime 17/12/2020 7:00:00

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Add crew details to date table

Jeron — Fri, 22 Jan 2021 01:02:24 GMT

Thanks a lot for your help