Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a problem dealing with nightshift hours. My sample table looks like this:
DatetimeStart | DatetimeFinish | Shift | Weekday | Mastercalendar | ORDERID |
13.06.2021 22:55:01 | 13.06.2021 23:45:01 | Nightshift | Sunday | 13.06.2021 | 12345 |
13.06.2021 23:00:00 | 13.06.2021 23:45:01 | Nightshift | Sunday | 13.06.2021 | 12346 |
14.06.2021 01:00:00 | 14.06.2021 02:00:00 | Nightshift | Monday | 14.06.2021 | 12347 |
14.06.2021 04:00:00 | 14.06.2021 05:00:00 | Nightshift | Monday | 14.06.2021 | 12348 |
14.06.2021 06:00:00 | 14.06.2021 11:00:00 | Dayshift | Monday | 14.06.2021 | 12349 |
14.06.2021 11:00:00 | 18.06.2021 13:00:00 | Dayshift | Monday | 14.06.2021 | 12350 |
15.06.2021 14:30:00 | 15.06.2021 15:00:00 | Lateshift | Tuesday | 15.06.2021 | 12351 |
16.06.2021 15:00:00 | 16.06.2021 17:00:00 | Lateshift | Wednesday | 16.06.2021 | 12352 |
17.06.2021 11:00:00 | 17.06.2021 14:00:00 | Dayshift | Thursday | 17.06.2021 | 12353 |
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.
Solved! Go to Solution.
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]
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
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]
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |