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.

     

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

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

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

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

      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