cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Breaking up day of week and hours to define shifts

I have data that I need to break into three shifts.

DAY SHIFT = Monday through Friday 5 AM - 5 PM

NIGHT SHIFT = Monday - Friday (into Saturday morning) 5 PM - 5 AM

WEEKEND: Saturday - Sunday (into Monday morning)   Time on this one will be Saturday morning starting at 5 AM to Monday morning at 5 AM

This is the formula I am trying:

Shifts = IF(
vwDowntime[timeonly2] >= TIME(5,00,00) && vwDowntime[timeonly2] < TIME(17,00,00) && vwDowntime[WeekDay] = 1 || 2 || 3 || 4 || 5, "DAY",
IF(vwDowntime[timeonly2] < TIME(5,00,00) && vwDowntime[WeekDay] = 2 || 3 || 4 || 5 || 6      ||     vwDownTime[timeonly2] >= TIME(17,00,00) && vwDownTime[WeekDay] = 1 || 2 || 3 || 4 || 5, "NIGHT",

"WEEKEND"))

What I am getting is nothing but day shift:

If anyone has suggestions on my formula or possibly an easier way to acheive my goal, please let me know. Appreciate any advice!

1 ACCEPTED SOLUTION
Super User

Am unable to see why your function doesn't work.

I recreated mine below and seems to work just fine.

``````Shift = SWITCH(
TRUE(),
[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] >= TIME(5,0,0 ) && [datetime] <= TIME(17, 0, 0 ), 1,
[Day] IN {"Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] < TIME(5,0,0 ) ||
[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] > TIME(17,0,0 ), 2,
3 )``````

ShiftDaydatetime

 1 Monday 5:00:00 AM 2 Tuesday 7:00:00 PM 3 Sunday 8:00:00 AM 2 Wednesday 11:00:00 PM 2 Tuesday 5:30:00 PM 1 Tuesday 5:30:00 AM 1 Tuesday 10:30:00 AM

9 REPLIES 9
Super User

@villasenorbritt , One way is to change the date in the column

New Date = if(timevalue([Datetime]) < time(5,0,0) , datevalue([Datetime]) -1, datevalue([Datetime]) )

Resolver I

@amitchandak I'm afraid that did not exactly answer my question. I should have specified that I already have a column that fixes the dates using a formula identicle to yours. I then turned them into day of the week but in number format, which is where the (DownTime[dayofweek]) is coming from. I need to know how to do my shifts. Can you please look at my formula and let me know where I went wrong?

Super User

My preference is to use the SWITCH function instead of nesting IF Statements.  Once you understand it, it becomes much cleaner and easier to work with.
I have started a statement which I hope you can work with:

``````Shift = SWITCH(
TRUE(),
[Weekday] IN {"Monday", "Tuesday" } && [Time] >= TIME(5,0,0 ) && [Time] <= TIME(17, 0, 0 ), "DAY",
"Other" )``````

Weekday    Time           Shift

 Monday 5:00:00 AM DAY Tuesday 7:00:00 PM Other Sunday 8:00:00 AM Other

If you can follow this pattern, I hope you will be able to make it work.  Otherwise, please post a sample of your vwDowntime table and I can then expand the SWITCH Statement for you.

Regards,

Resolver I

@rsbin Thank for your reply. I'm not sure what the problem is, but to try your solution, I attempted the following formula:

ShiftsSwitch = SWITCH(
TRUE(),
[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && vwDowntime[datetime] >= TIME(5,00,00) && vwDowntime[datetime] < TIME(17,00,00), 1,
[Day] IN {"Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"} && vwDowntime[datetime] < TIME(5,00,00) || [Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && vwDowntime[datetime] >= TIME(17,00,00), 2,
3)

Shifts that are running on a Tuesday morning at 10:30 AM are showing up as 2nd shift. Any suggestions?
Resolver I

@rsbin I appreciate your help so much! I was actually using a "timeOnly" column in my data that had the data type of Date/Time. Once I switched it over to time only, this formula did exactly what I was wanting it to, thanks to you!

Resolver I

And for anyone who is curious,

@amitchandak formula will correct the shift behavior so that the data gets accounted for in the correct shift.

I then extracted the day name from the Real Shift Date.

You can then use @rsbin formula to complete defining your shifts. I used the column that I extracted from the Real Shift Dates called "Real Shift Day Name" and a timeOnly column.

To avoid my mistakes, make sure that data types are correct! This is why the formula did not work at first.

Below is my formula:

Shift = SWITCH(
TRUE(),
[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [timeonly] >= TIME(5,0,0 ) && [timeonly] < TIME(17, 0, 0 ), "1st Shift",
[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"} && [timeonly] < TIME(5,0,0 ) ||
[Real Shift Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [timeonly] >= TIME(17,0,0 ), "2nd Shift",
"Weekend" )
Super User

My pleasure...glad it finally worked out for you.

Super User

Am unable to see why your function doesn't work.

I recreated mine below and seems to work just fine.

``````Shift = SWITCH(
TRUE(),
[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] >= TIME(5,0,0 ) && [datetime] <= TIME(17, 0, 0 ), 1,
[Day] IN {"Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] < TIME(5,0,0 ) ||
[Day] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday" } && [datetime] > TIME(17,0,0 ), 2,
3 )``````

ShiftDaydatetime

 1 Monday 5:00:00 AM 2 Tuesday 7:00:00 PM 3 Sunday 8:00:00 AM 2 Wednesday 11:00:00 PM 2 Tuesday 5:30:00 PM 1 Tuesday 5:30:00 AM 1 Tuesday 10:30:00 AM

Super User

In a meeting for the next little bit.  Will have a closer look when I am able.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors