Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Conversion of Time to equivalent shift

Hi,   Would like to know if how will I make a column for the equivalent shift of the given time   1st Shift: 6am - 2pm 2nd Shift: 2pm - 10pm 3rd Shift: 10pm - 6am   Thank you
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Anonymous 

    you can try this in PQ

     

    =if DateTime.Time([Date]) >= #time(6, 0, 0) and DateTime.Time([Date]) < #time(14,0,0) then "1st Shift" else if DateTime.Time([Date]) >= #time(14,0,0) and DateTime.Time([Date]) <#time(22,0,0) then "2nd Shift" else "3rd Shift"

     

     

    or create a DAX column

    Column =
    VAR _time='Table'[Date]-int('Table'[Date])
    return if(_time>=time(6,0,0) && _time<time(14,0,0), "1st Shift", if(_time>=time(14,0,0)&& _time<time(22,0,0),"2nd Shift", "3rd Shift"))
     
     
    pls see the attachment below
     

     

  • Chakravarthy's avatar
    Chakravarthy
    2 years ago

    Hi Anonymous 

    Alternatively you can create calculated column as below using TIME DAX functions:

    Shift =
    VAR _HOUR = HOUR('Table'[Date])
    VAR _MIN = MINUTE('Table'[Date])
    VAR _SEC = SECOND('Table'[Date])
    VAR _TIME = TIME(_HOUR,_MIN,_SEC)
    RETURN
    SWITCH(TRUE(),
    _TIME>=TIME(6,00,00) && _TIME<TIME(14,00,00),"1st Shift",
    _TIME>=TIME(14,00,00) && _TIME<TIME(22,00,00),"2nd Shift",
    "3rd Shift"
    )
Chakravarthy's avatar
Chakravarthy
Icon for Resolver II rankResolver II
2 years ago

Hi Anonymous 

Alternatively you can create calculated column as below using TIME DAX functions:

Shift =
VAR _HOUR = HOUR('Table'[Date])
VAR _MIN = MINUTE('Table'[Date])
VAR _SEC = SECOND('Table'[Date])
VAR _TIME = TIME(_HOUR,_MIN,_SEC)
RETURN
SWITCH(TRUE(),
_TIME>=TIME(6,00,00) && _TIME<TIME(14,00,00),"1st Shift",
_TIME>=TIME(14,00,00) && _TIME<TIME(22,00,00),"2nd Shift",
"3rd Shift"
)