Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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 |
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
Thanks a lot for your help
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
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |