Forum Discussion

jhaldane's avatar
jhaldane
Frequent Visitor
8 years ago
Solved

Group Days and create shifts

Please can you assist me (step by step) Please can you show me how to group into days and then create three shifts. i.e. I have many rows for the 1st May all with different times. Then group times ...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi jhaldane,

     

    Month = Test_3[Dates].[MonthNo]

Day = Test_3[Dates].[Day] 

Time = FORMAT(Test_3[Dates] ,"hh:mm:ss")

Shift =
IF (
    Test_3[Time] >= TIME ( 6, 0, 0 )
        && Test_3[Time] < TIME ( 14, 0, 0 ),
    "Shift1",
    IF (
        Test_3[Time] >= TIME ( 14, 0, 0 )
            && Test_3[Time] < TIME ( 22, 0, 0 ),
        "Shift2",
        "Shift3"
    )
)

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
4 years ago

This may help:

Step 1 : Create a new column by using the following code (change the times as you want) 

Shift =
IF (
[Time LOCAL] >= TIME ( 6, 0, 0 )
&& [Time LOCAL] < TIME ( 14, 0, 0 ),
"Shift1",
IF (
[Time LOCAL] >= TIME ( 14, 00, 0 )
&& [Time LOCAL] < TIME ( 22, 0, 0 ),
"Shift2",
"Shift3"
)
)

Step 2: Create another column as Payday - this column will accomodate the shift information and date


PayDay = IF(
Query1[Shift] = "Shift1" || Query1[Shift]= "Shift2" , (Query1[Date]),
IF(Query1[Time LOCAL] >= TIME(22,00,00) && Query1[Shift] = "Shift3",
DATEADD(Query1[Date], +1, DAY),
IF(Query1[Time LOCAL] <= TIME(06,00,00) && Query1[Shift] = "Shift3" ,(Query1[Date]), Query1[Date])))