Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jhaldane
Frequent Visitor

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 into 3 shifts. Shift 1 06:00-14:00, Shift 2 14:00 - 22:00 and SHift 3 22:00 - 06:00

 

Objective is to have a slicer where i can choose month, day and shift to see any abnormalities

Would really appreciate your support step by step

Thanks

Jerry

 

Hello, image.png

 

 

 

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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"
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
harshaduggi
Frequent Visitor

I want to create something similar,

what i want is

shift1 - 7:30 AM  - 7:30 PM

shift2 - 7:30PM - 7:30AM,
so when user1 select shift1 then the report has to filter yesterday shift1 - 7:30 AM  - 7:30 PM and shift 2 should filter from yesterday evening 7:30PM - 7:30AM(today Morning).

hrishi
Helper I
Helper I

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])))



hrishi
Helper I
Helper I

The problem that i am facing is that 3rd shift time overlaps between two days and the 3rd shift data has to be accounted for on the day the shift started. 
Could you please guide me on how to do that. 

I have facing the same problem. did get the solution?

That means the third shift start on the day before I have selected (10:01 pm to 12:00 pm) and continues from 12:01 to 05:59 from the day selected.

 

v-yulgu-msft
Microsoft Employee
Microsoft Employee

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"
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, this did not work for me as I continue to get an error that states operations do not support comparing values of type Text with values of type Date.  It says to consider using the Value or Format function to convert.  But then when I convert the Time column as you have suggested, it also gives me an error that says "Cannot covert value " of type Text to type Date".  Please help, I've done exactly what you've suggested and I cannot get it to work.

Hi, this does not work for me.  I have been trying this for about 30 minutes, and it continues to give me an error "cannot convert value " of type Text to type Date" when I try to convert the text to "time" as displayed in your example.  Can you help? I've done EXACTLY what you've suggested here.  Thanks.

Thank you so much for responding.

 

Some questions on below.

 

I have managed to add the columns for month and time.

 

Please can you assist me with the column for 'shift'?

Where do i type in the formula for Shift?

What measure should the shift column be in?

 

 

Thanks

Jerry

 

 

 

Month = Test_3[Dates].[MonthNo]

Day = Test_3[Dates].[Day] 

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

 

Hi @jhaldane,

 

For all above formulas in my original post, you should type them into calculated columns via clicking the highlighted button.

1.PNG

 

Create the calculated column: Month, Day and Time one by one. Then, remember to set the data type of [Time] field, as I mentioned in original post. After that, you can create the calculated column [Shift].

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jhaldane
Frequent Visitor

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 into 3 shifts. Shift 1 06:00-14:00, Shift 2 14:00 - 22:00 and SHift 3 22:00 - 06:00

 

Objective is to have a slicer where i can choose month, day and shift to see any abnormalities

Would really appreciate your support step by step

Thanks

Jerry

 

 image.png

Thank you

Jerry

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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