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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Applicable88
Impactful Individual
Impactful Individual

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
Solution Sage
Solution Sage

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors