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
ivannye
Helper I
Helper I

Day 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.

ivannye_0-1613355972480.png

you can find the powerbi report here.

https://1drv.ms/u/s!Aqd0HyltQcBtii8kQChgPgYJ9K0R?e=I5NcTG

thank you for your help

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

 

View solution in original post

8 REPLIES 8
v-cazheng-msft
Community Support
Community Support

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.

 

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
Super User
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)

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.

 

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

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

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

Thank you. Here is the result below.

ivannye_0-1613363611753.png

 

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

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.