Day shift and night shift

[ivannye]

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

[v-cazheng-msft · Accepted solution]

Hi, @ivannye 

I've made some changes to these two dax formulas. Here is the pbix file.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[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

[amitchandak]

@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)

[ivannye]

Hi 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)
)

[ivannye]

Thank you for your reply. above you wrote starttime and endtime. do you mean start_time and end_time?

[amitchandak]

@ivannye , yes, You start_time and End_time columns. Please choose the column's name as in your file. Diff is in seconds

[ivannye]

Thank you. Here is the result below.

As you can see the solution on both column duration_day and duration_night are the same. durationday and durationnight are manual calculations