Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Jeron
Frequent Visitor

Add crew details to date table

‎01-18-2021 07:27 PM

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!Date table with crew details.PNG

 

Message 1 of 5
398 Views
0
2 ACCEPTED SOLUTIONS
Jeron
Frequent Visitor
In response to AlB

‎01-19-2021 04:41 PM

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.

 

DateShiftStartTimeShiftEndTimeShiftCrew
03/12/202012/03/2020 7:0012/03/2020 18:59DAYA
03/12/202012/03/2020 19:0012/04/2020 6:59NIGHTB
04/12/202012/04/2020 7:0012/04/2020 18:59DAYA
04/12/202012/04/2020 19:0012/05/2020 6:59NIGHTB
05/12/202012/05/2020 7:0012/05/2020 18:59DAYA
05/12/202012/05/2020 19:0012/06/2020 6:59NIGHTB
06/12/202012/06/2020 7:0012/06/2020 18:59DAYA
06/12/202012/06/2020 19:0012/07/2020 6:59NIGHTB
07/12/202012/07/2020 7:0012/07/2020 18:59DAYA
07/12/202012/07/2020 19:0012/08/2020 6:59NIGHTB
08/12/202012/08/2020 7:0012/08/2020 18:59DAYA
08/12/202012/08/2020 19:0012/09/2020 6:59NIGHTB
09/12/202012/09/2020 7:0012/09/2020 18:59DAYA
09/12/202012/09/2020 19:0012/10/2020 6:59NIGHTB
10/12/202012/10/2020 7:0012/10/2020 18:59DAYC
10/12/202012/10/2020 19:0012/11/2020 6:59NIGHTD
11/12/202012/11/2020 7:0012/11/2020 18:59DAYC
11/12/202012/11/2020 19:0012/12/2020 6:59NIGHTD
12/12/202012/12/2020 7:0012/12/2020 18:59DAYC
12/12/202012/12/2020 19:0012/13/2020 6:59NIGHTD
13/12/202012/13/2020 7:0012/13/2020 18:59DAYC
13/12/202012/13/2020 19:0012/14/2020 6:59NIGHTD
14/12/202012/14/2020 7:0012/14/2020 18:59DAYC
14/12/202012/14/2020 19:0012/15/2020 6:59NIGHTD
15/12/202012/15/2020 7:0012/15/2020 18:59DAYC
15/12/202012/15/2020 19:0012/16/2020 6:59NIGHTD
16/12/202012/16/2020 7:0012/16/2020 18:59DAYC
16/12/202012/16/2020 19:0012/17/2020 6:59NIGHTD
17/12/202012/17/2020 7:0012/17/2020 18:59DAYB
17/12/202012/17/2020 19:0012/18/2020 6:59NIGHTA
18/12/202012/17/2020 7:0012/17/2020 18:59DAYB
18/12/202012/18/2020 19:0012/19/2020 6:59NIGHTA
19/12/202012/19/2020 7:0012/19/2020 18:59DAYB
19/12/202012/19/2020 19:0012/20/2020 6:59NIGHTA
20/12/202012/20/2020 7:0012/20/2020 18:59DAYB
20/12/202012/20/2020 19:0012/21/2020 6:59NIGHTA
21/12/202012/21/2020 7:0012/21/2020 18:59DAYB
21/12/202012/21/2020 19:0012/22/2020 6:59NIGHTA
22/12/202012/22/2020 7:0012/22/2020 18:59DAYB
22/12/202012/22/2020 19:0012/23/2020 6:59NIGHTA
23/12/202012/23/2020 7:0012/23/2020 18:59DAYB
23/12/202012/23/2020 19:0012/24/2020 6:59NIGHTA
24/12/202012/24/2020 7:0012/24/2020 18:59DAYD
24/12/202012/24/2020 19:0012/25/2020 6:59NIGHTC
25/12/202012/25/2020 7:0012/25/2020 18:59DAYD
25/12/202012/25/2020 19:0012/26/2020 6:59NIGHTC
26/12/202012/26/2020 7:0012/26/2020 18:59DAYD
26/12/202012/26/2020 19:0012/27/2020 6:59NIGHTC
27/12/202012/27/2020 7:0012/27/2020 18:59DAYD
27/12/202012/27/2020 19:0012/28/2020 6:59NIGHTC
28/12/202012/28/2020 7:0012/28/2020 18:59DAYD
28/12/202012/28/2020 19:0012/29/2020 6:59NIGHTC
29/12/202012/29/2020 7:0012/29/2020 18:59DAYD
29/12/202012/29/2020 19:0012/30/2020 6:59NIGHTC
30/12/202012/30/2020 7:0012/30/2020 18:59DAYD
30/12/202012/30/2020 19:0012/31/2020 6:59NIGHTC

View solution in original post

Message 3 of 5
337 Views
0
AlB
Super User
Super User

‎01-20-2021 01:15 AM

@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 

 

SU18_powerbi_badge

View solution in original post

Message 4 of 5
317 Views
0
4 REPLIES 4
Jeron
Frequent Visitor

‎01-21-2021 05:02 PM

Thanks a lot for your help 

Message 5 of 5
295 Views
0
AlB
Super User
Super User

‎01-20-2021 01:15 AM

@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 

 

SU18_powerbi_badge

Message 4 of 5
318 Views
0
AlB
Super User
Super User

‎01-19-2021 05:22 AM

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 

 

SU18_powerbi_badge

Message 2 of 5
346 Views
0
Jeron
Frequent Visitor
In response to AlB

‎01-19-2021 04:41 PM

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.

 

DateShiftStartTimeShiftEndTimeShiftCrew
03/12/202012/03/2020 7:0012/03/2020 18:59DAYA
03/12/202012/03/2020 19:0012/04/2020 6:59NIGHTB
04/12/202012/04/2020 7:0012/04/2020 18:59DAYA
04/12/202012/04/2020 19:0012/05/2020 6:59NIGHTB
05/12/202012/05/2020 7:0012/05/2020 18:59DAYA
05/12/202012/05/2020 19:0012/06/2020 6:59NIGHTB
06/12/202012/06/2020 7:0012/06/2020 18:59DAYA
06/12/202012/06/2020 19:0012/07/2020 6:59NIGHTB
07/12/202012/07/2020 7:0012/07/2020 18:59DAYA
07/12/202012/07/2020 19:0012/08/2020 6:59NIGHTB
08/12/202012/08/2020 7:0012/08/2020 18:59DAYA
08/12/202012/08/2020 19:0012/09/2020 6:59NIGHTB
09/12/202012/09/2020 7:0012/09/2020 18:59DAYA
09/12/202012/09/2020 19:0012/10/2020 6:59NIGHTB
10/12/202012/10/2020 7:0012/10/2020 18:59DAYC
10/12/202012/10/2020 19:0012/11/2020 6:59NIGHTD
11/12/202012/11/2020 7:0012/11/2020 18:59DAYC
11/12/202012/11/2020 19:0012/12/2020 6:59NIGHTD
12/12/202012/12/2020 7:0012/12/2020 18:59DAYC
12/12/202012/12/2020 19:0012/13/2020 6:59NIGHTD
13/12/202012/13/2020 7:0012/13/2020 18:59DAYC
13/12/202012/13/2020 19:0012/14/2020 6:59NIGHTD
14/12/202012/14/2020 7:0012/14/2020 18:59DAYC
14/12/202012/14/2020 19:0012/15/2020 6:59NIGHTD
15/12/202012/15/2020 7:0012/15/2020 18:59DAYC
15/12/202012/15/2020 19:0012/16/2020 6:59NIGHTD
16/12/202012/16/2020 7:0012/16/2020 18:59DAYC
16/12/202012/16/2020 19:0012/17/2020 6:59NIGHTD
17/12/202012/17/2020 7:0012/17/2020 18:59DAYB
17/12/202012/17/2020 19:0012/18/2020 6:59NIGHTA
18/12/202012/17/2020 7:0012/17/2020 18:59DAYB
18/12/202012/18/2020 19:0012/19/2020 6:59NIGHTA
19/12/202012/19/2020 7:0012/19/2020 18:59DAYB
19/12/202012/19/2020 19:0012/20/2020 6:59NIGHTA
20/12/202012/20/2020 7:0012/20/2020 18:59DAYB
20/12/202012/20/2020 19:0012/21/2020 6:59NIGHTA
21/12/202012/21/2020 7:0012/21/2020 18:59DAYB
21/12/202012/21/2020 19:0012/22/2020 6:59NIGHTA
22/12/202012/22/2020 7:0012/22/2020 18:59DAYB
22/12/202012/22/2020 19:0012/23/2020 6:59NIGHTA
23/12/202012/23/2020 7:0012/23/2020 18:59DAYB
23/12/202012/23/2020 19:0012/24/2020 6:59NIGHTA
24/12/202012/24/2020 7:0012/24/2020 18:59DAYD
24/12/202012/24/2020 19:0012/25/2020 6:59NIGHTC
25/12/202012/25/2020 7:0012/25/2020 18:59DAYD
25/12/202012/25/2020 19:0012/26/2020 6:59NIGHTC
26/12/202012/26/2020 7:0012/26/2020 18:59DAYD
26/12/202012/26/2020 19:0012/27/2020 6:59NIGHTC
27/12/202012/27/2020 7:0012/27/2020 18:59DAYD
27/12/202012/27/2020 19:0012/28/2020 6:59NIGHTC
28/12/202012/28/2020 7:0012/28/2020 18:59DAYD
28/12/202012/28/2020 19:0012/29/2020 6:59NIGHTC
29/12/202012/29/2020 7:0012/29/2020 18:59DAYD
29/12/202012/29/2020 19:0012/30/2020 6:59NIGHTC
30/12/202012/30/2020 7:0012/30/2020 18:59DAYD
30/12/202012/30/2020 19:0012/31/2020 6:59NIGHTC
Message 3 of 5
338 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All