Conversion of Time to equivalent shift

[Anonymous]

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

[ryan_mayu · Accepted solution]

@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 · Accepted solution]

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"

)

[ryan_mayu · Accepted solution]

@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