Shift that Goes into Next day

[villasenorbritt]

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!

[villasenorbritt · Accepted solution]

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. 

[lbendlin]

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.

[villasenorbritt]

@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.

[lbendlin]

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 Custom" = Table.AddColumn(#"Changed Type", "TimeStamp", each [dateonly] & [time]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Shifted", each [TimeStamp]-#duration(0,5,0,0)),
    #"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
    #"Added Custom2"

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