Forum Discussion

georgec96's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago
Solved

Split day into 4 Shifts (A,B,C,D)

Hi everyone,

I just started working in Power BI and would need some guidance.

I have a table with 4 columns: Date (day and time) user, type and quantity.

I need to split this into 4 shifts and then group and sum the quantity by user, date, shift, type. I have tried using the conditional column in Power Query but I had no luck getting the result I wanted, also because the night shift goes over 2 nights when I group my data it doesnt include both days/nights.

 

A shift = Mon-Friday 06:00:00-14:00:00

B shift = Mon-Friday 14:00:00-22:00:00

C shift = Sun-Fri 22:00:00-06:00:00

D shift = Sat and Sun 06:00:00-18:00:00

 

 

Any help would be greatly appreciated.

Thank you in advance.

Need Help
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi georgec96 ,

     

    I modify amitchandak 's formula, please try it.

     

    New column = 
var _day = weekday([trndte],2)
var _time = timevalue([trndte])
return
switch(
    True() ,
    _day < 6 && _time <= time(14,0,0) && _time > time(6,0,0) , "A Shift",
    _day < 6 && _time <= time(22,0,0) && _time > time(14,0,0) , "B Shift",
    ( _day < 5 && ( _time > time(22,0,0) || _time <= time(6,0,0) ) ) || ( _time > time(22,0,0) && _day = 7 ) || ( _time <= time(6,0,0) && _day = 5 ), "C Shift",
    _day in {6,7} && _time <= time(18,0,0) && _time > time(6,0,0) , "D Shif"
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

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

    georgec96 , Create a new column like

     

    New column =
    var _day = weekday([trndate],2)
    var _time = timevalue([trndate])
    return
    switch(True() ,
    _day < 6 , _time <=time(14,0,0) && _time >time(6,0,0) , "A Shift",
    _day < 6 , _time <=time(22,0,0) && _time >time(14,0,0) , "B Shift",
    _day < 6 || _day =7, _time >time(22,0,0) && _time <=time(6,0,0) , "B Shift",
    _day in {6,7} , _time <=time(18,0,0) && _time >time(6,0,0) , "D Shif"
    )

    • georgec96's avatar
      georgec96
      Icon for Helper II rankHelper II
      4 years ago

      Thank you for your quick reply amitchandak , but when I create a new column I am getting the following error.

       

      "Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

       

      Column trndte is already formatted as Date/Time

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi georgec96 ,

         

        I modify amitchandak 's formula, please try it.

         

        New column = 
var _day = weekday([trndte],2)
var _time = timevalue([trndte])
return
switch(
    True() ,
    _day < 6 && _time <= time(14,0,0) && _time > time(6,0,0) , "A Shift",
    _day < 6 && _time <= time(22,0,0) && _time > time(14,0,0) , "B Shift",
    ( _day < 5 && ( _time > time(22,0,0) || _time <= time(6,0,0) ) ) || ( _time > time(22,0,0) && _day = 7 ) || ( _time <= time(6,0,0) && _day = 5 ), "C Shift",
    _day in {6,7} && _time <= time(18,0,0) && _time > time(6,0,0) , "D Shif"
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.