cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Post Prodigy
Post Prodigy

Same shift time but different day

Hello,

I have a problem dealing with shift days. My sample table looks like this:

 

DatetimeStartDatetimeFinishShift WeekdayMastercalendarORDERID
13.06.2021 22:55:0113.06.2021 23:45:01NightshiftSunday13.06.202112345
13.06.2021 23:00:0013.06.2021 23:45:01NightshiftSunday13.06.202112346
14.06.2021 01:00:0014.06.2021 02:00:00NightshiftMonday14.06.202112347
14.06.2021 04:00:0014.06.2021 05:00:00NightshiftMonday14.06.202112348
14.06.2021 06:00:0014.06.2021 11:00:00DayshiftMonday14.06.202112349
14.06.2021 11:00:0018.06.2021 13:00:00DayshiftMonday14.06.202112350
15.06.2021 14:30:0015.06.2021 15:00:00LateshiftTuesday15.06.202112351
16.06.2021 15:00:0016.06.2021 17:00:00LateshiftWednesday16.06.202112352
17.06.2021 11:00:0017.06.2021 14:00:00DayshiftThursday17.06.202112353

The shift column is build very typically with if statment:

When hour is from 6 to 14, "Dayshift", 14 to 22 "Lateshift", 22 to 06, "Nightshift".

My problem is... when I want to know the counts of all orders or duration etc. of Monday nightshift (which starts on a Sunday), semantically the time actually starts from the same night one day before from 22:00:00 on. And the counts of orders or the duration of start and finishtimes, also belongs to the nightshift which last until next day morning 06:00 o'clock. 

That means whatever nightshift I want to looka at , or counting of orders etc. it should start from one day before at 22:00:00. 

 

What is best practice here? 

Thank you very much in advance. 

Best. 

 

3 REPLIES 3
Jakinta
Super User
Super User

Probably there is a neater solution.

Until someone provides it, you can try with steps below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdA9C4MwEIDhv1Iyi1y+beaObZcKHcRB0FYXC1UH/30tMSbGQJFClnsJD9xlGcI0BhETIPhAiOJcAUbRqlLF5nptnnXf1c2jn4bb0JbFuPr7HQhlHOVR5hEA0/sXFhpmCwHYwk4lS13Bl5eBmQfLDcyCMN8LJxtYhGBs9zgV42/26LMOgBNb6S6Wg2a5BZiihnWqPcO56CvjpkPVzTD3YKxhESLcKoPwvSrbhRYeTTQtg6eQ7iaBU6T18Daw9GCK8vwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DatetimeStart = _t, DatetimeFinish = _t, #"Shift " = _t, Weekday = _t, Mastercalendar = _t, ORDERID = _t]),
    Removed = Table.RemoveColumns(Source,{"Shift ", "Weekday", "Mastercalendar"}),
    Locale = Table.TransformColumnTypes(Removed, {{"DatetimeStart", type datetime},{"DatetimeFinish", type datetime}}, "hr-HR"),
    AddedHrs = Table.TransformColumns(Locale,{{"DatetimeStart", each _ + #duration(0,2,0,0), type datetime}, {"DatetimeFinish", each _ + #duration(0,2,0,0), type datetime}}),
    ShiftCol = Table.AddColumn(AddedHrs, "Shift", each if [DatetimeStart]>= DateTime.From(Date.From([DatetimeStart]))  and [DatetimeStart]< DateTime.From(Date.From([DatetimeStart])) + #duration(0,8,0,0)
then "Nifghtshift"
else if [DatetimeStart]>= DateTime.From(Date.From([DatetimeStart])+ #duration(0,8,0,0))  and [DatetimeStart]< DateTime.From(Date.From([DatetimeStart])) + #duration(0,16,0,0)
then "Dayshift"
else "Latehift"),
    WeekdayCol = Table.AddColumn(ShiftCol, "Weekday", each Date.DayOfWeekName([DatetimeStart])),
    #"Added Custom" = Table.AddColumn(WeekdayCol, "Mastercalendar", each Date.From([DatetimeStart]), type date),
    Reset = Table.TransformColumns(#"Added Custom",{{"DatetimeStart", each _ - #duration(0,2,0,0), type datetime}, {"DatetimeFinish", each _ - #duration(0,2,0,0), type datetime}}),
    FINAL = Table.ReorderColumns(Reset,{"DatetimeStart", "DatetimeFinish", "Shift", "Weekday", "Mastercalendar", "ORDERID"})
in
    FINAL

Hello @Jakinta , thank you for the quick reply. The first part with "binary.FromText..." and then the long code...where does it come from. And did you had to write the code manually or is it possible to get there with clicking through PowerQuery Editor?

Best.

Hi, 

regarding your 1st q please look at here.

Yes, I had to write code manually.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors