Forum Discussion
Helper IDay shift and night shift
Hi Everyone,
thank you for your help. I really appreciate it.
I have an issue I would like to submit to you. I started working on it but one is very tricky. I have two columns start_time and end_time. the goal is to separate them between the duration day (between 7am and 9pm) and duration night (between 9pm and 7 am). they are all in second. I succeeded for most but the issue arose when the end time finishes next day. Do you have a quicker solution? Thank you.
you can find the powerbi report here.
https://1drv.ms/u/s!Aqd0HyltQcBtii8kQChgPgYJ9K0R?e=I5NcTG
thank you for your help
8 Replies
- amitchandak
Super User
ivannye , One of the DateTime, Start or end has to drive it like start
Duartion Night =if(Hour([StartTime]) >= 7 && Hour([StartTime]) <=21,0,1)
end
Duartion Night =if(Hour([EndTime]) >= 7 && Hour([EndTime]) <=21,0,1)
ivannyeHi Thank you for your quick reply. duration day should look at start_time and returns the number of second between if start time falls between 7 to 9PM. for example if start time is 1/01/2020, 8:45:00pm and end time if 1/01/2020, 21:10:00pm, the duration day is 15minutes*60 and duration night is 10minutes*60.
- amitchandak
ivannye , Try two new columns like
Duration Night =
Switch(true() ,
Hour([StartTime]) <= 21 && Hour([EndTime]) >=21 , datediff(date(year([start time]), month([start time]), day([start time])) + time(21,0,0), [Endtime], second),
Hour([StartTime]) <= 7 && Hour([EndTime]) >=7 , datediff([start time], date(year([Endtime]), month([Endtime]), day([Endtime])) + time(7,0,0), second),
datediff([start time], [Endtime] , second)
)
Duration Day =
Switch(true() ,
Hour([StartTime]) <= 21 && Hour([EndTime]) >=21 , datediff([start time], date(year([Endtime]), month([Endtime]), day([Endtime])) + time(21,0,0), second) ,
Hour([StartTime]) <= 7 && Hour([EndTime]) >=7 ,datediff(date(year([start time]), month([start time]), day([start time])) + time(7,0,0), [Endtime], second),
datediff([start time], [Endtime] , second)
)
v-cazheng-msftCommunity Support
- ivannye
Thank you for your reply. it was really helpful. is it possible to divide morning into morning 7am to 2:30pm and afternoon 2:30pm to 9pm?
I made a modification but could not make it work well.
https://1drv.ms/u/s!Aqd0HyltQcBtijNF6faJUen12jsY?e=HQdYRn
Thank you
