Forum Discussion

ivannye's avatar
ivannye
Icon for Helper I rankHelper I
5 years ago
Solved

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...
Need Help
Tips and Tricks
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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's avatar
ivannye
Icon for Helper I rankHelper I
5 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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