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

  • 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