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 , 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'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