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

## Shift that Goes into Next day

I need to break up the days in my data by shift, which I was able to do. The problem is that the morning shift goes from 5 AM until 5 PM, and night shift goes from 5PM to 5 AM. Power Bi is lumping the portion of night shift that goes into the next day as the next day's data. This is the formula I am trying to get to work, although I'm not sure if I'm going in the right direction:

Shift2 = IF(DownTime[Time] >= TIME(5,0,0) && DownTime[Time] <= TIME(17,0,0), "1st Shift", IF(DownTime[Time] >= TIME(17,0,0) && DownTime[Time]<= TIME(23,59,0), "2nd Shift", IF(DownTime[Time] >= TIME(0,0,0) && DownTime[Time] <= TIME(4,59,00), "2nd Shift"; (DownTime[dateonly]-1;DownTime[dateonly])

The error is showb below at the ";"  ------

Here is some sample data:

 dateonly time production 8/5/2022 13:30 5 8/5/2022 15:00 5 8/5/2022 21:00 10 8/6/2022 2:00 6 8/6/2022 3:00 8
For example, (this is military time), the last two entries should technically be included for 2nd shift on August 5th, but it will store the data as August 6th. Can someone take a look at the formula I posted above and see where the error is and how I can fix it? I have a feeling it is something silly and I've been working at this for longer than I'd like to admit. Thanks!
1 ACCEPTED SOLUTION
Resolver I

Although the method above was doable, I found an easier solution. I simply made a new custom column with the following formula:

correctShiftDate = if DateTime.Time([startdate]) <= #time(5,00,0) then Date.AddDays(DateTime.Date([startdate]), -1) else DateTime.Date([startdate]))

The following output is:

As you can see, the formula takes the shifts that happen anywhere between 12 AM and 5 AM and counts that as the day before in order to correctly identify production and downtime according to shift and date.

4 REPLIES 4
Resolver I

Although the method above was doable, I found an easier solution. I simply made a new custom column with the following formula:

correctShiftDate = if DateTime.Time([startdate]) <= #time(5,00,0) then Date.AddDays(DateTime.Date([startdate]), -1) else DateTime.Date([startdate]))

The following output is:

As you can see, the formula takes the shifts that happen anywhere between 12 AM and 5 AM and counts that as the day before in order to correctly identify production and downtime according to shift and date.

Super User

``````Shift = SWITCH(TRUE(),
DownTime[Time] >= TIME(5,0,0) && DownTime[Time] < TIME(17,0,0), "1st Shift",
"2nd Shift")``````

You can fix the day assignment by shifting (ha, funny) the time five hours back during the daily computation.

Resolver I

@lbendlin That formula gives me the correct shift, but does not adjust the data that falls into the next day to the previous. When you say shifting the time five hours back, what do you mean? Could you give an example? Thank you for your help.

Super User
``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31TcyMDJS0lEyNLYyNgDSpkqxOqgSplYGWCWMDCEShgZQGTO4DETCDF3cGCJuoRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dateonly = _t, time = _t, production = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"dateonly", type date}, {"time", type time}}),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Shift", each if Time.Hour([Shifted])<12 then Text.From (Date.From([Shifted]) ) & " " & "First shift" else Text.From (Date.From([Shifted]) ) & " " & "Second shift")
in

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

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.