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

Allocate the nightshift hours to the correct day

Hello,

I have a problem dealing with nightshift hours. 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".

But there is also another condition, which I don't know how to solve. For example nightshift of Mondays starts actually on Sunday 22PM and last until Monday morning 6AM. And this is true for every following nightshift, where the starting time is actually in the evening of the night before. So for instance, when  I want to filter my data to Monday the 14th of June and want to count the orders or sum up the duration time of that nightshift, unfortunately it displays me the nightshift from Monday 12:00:00 -6AM plus the data of the same day Monday 22PM-24PM. So these two time ranges are not belonging together, since the hours of 22PM-24PM of that very same day actually are already belonging to the nightshift of Tuesday. For Monday the nightshift starts at Sunday 13th 22PM until Monday 14th 6:00AM.

 

What is best practice here? Aynone encounter same issue before?

Thank you very much in advance. 

Best. 

 

2 ACCEPTED SOLUTIONS
MFelix
Super User III
Super User III

Hi @Applicable88 ,

 

Believe that for this case you need to create a new column with the correct date, in this case you need to check if the hour is higher than the 22 PM and if yes return one addtional day:

 

if Time.Hour([DatetimeStart]) >= 22 then [Mastercalendar] + #duration(1,0,0,0)else [Mastercalendar]

 

MFelix_0-1623928268208.png

 

Now you should use this column to make your filtering. since you are no the query editor you can delete the other colunm from you table


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hello @MFelix , yes I cam to the same conclusion whereas I think your right,  I could have save the smaller than 24 since after 24 its start at zero again:

if Time.Hour([StartTime])>=22 and Time.Hour([StartTime]) <= 24 then Date.AddDays([StartTime],1) else [StartTime]

Thank you.

Best.

View solution in original post

2 REPLIES 2
MFelix
Super User III
Super User III

Hi @Applicable88 ,

 

Believe that for this case you need to create a new column with the correct date, in this case you need to check if the hour is higher than the 22 PM and if yes return one addtional day:

 

if Time.Hour([DatetimeStart]) >= 22 then [Mastercalendar] + #duration(1,0,0,0)else [Mastercalendar]

 

MFelix_0-1623928268208.png

 

Now you should use this column to make your filtering. since you are no the query editor you can delete the other colunm from you table


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hello @MFelix , yes I cam to the same conclusion whereas I think your right,  I could have save the smaller than 24 since after 24 its start at zero again:

if Time.Hour([StartTime])>=22 and Time.Hour([StartTime]) <= 24 then Date.AddDays([StartTime],1) else [StartTime]

Thank you.

Best.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors