topic Re: Create shift column depending on the time in DAX Commands and Tips

Create shift column depending on the time

pagliaci — Wed, 29 Sep 2021 13:19:28 GMT

Could someone help, please? I am creating a new column that I want to display shift "1" "2" or "3" depending on the time of my production order. I tried to use

Turno =

/* Shift 1 6:20:00 - 14:20:00*/

/* Shift 2 14:20:01 - 22:20:00*/

/* Shift 3 22:20:01 - 06:19:59*/

IF (

HOUR ( [Hora PO] ) >= 6

&& HOUR ( [Hora PO] ) <= 14,

IF (

HOUR ( [Hora PO] ) > 14

&& HOUR ( [Hora PO] ) <= 22,

2, 3))

But 2 things are wrong: My column "Turno" has always the value 3 and I can´t compare the minutes (in red), just full hours. I need to have the exactly shift times:

Shift 1 6:20:00 - 14:20:00*/

/* Shift 2 14:20:01 - 22:20:00*/

/* Shift 3 22:20:01 - 06:19:59*/

Thank you so much!!!

Re: Create shift column depending on the time

VahidDM — Wed, 29 Sep 2021 13:24:44 GMT

Hi pagliaci

Use variables:

Measure = Var _A= time(6,20,0) Var _B = time(14,20,00) Var _C = time(22,20,00) Var _D = time(06,19,59) IF ( HOUR ( [Hora PO] ) >= _A && HOUR ( [Hora PO] ) <= _B, 1, IF ( HOUR ( [Hora PO] ) > _B && HOUR ( [Hora PO] ) <= _C, 2, 3))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Create shift column depending on the time

pagliaci — Mon, 04 Oct 2021 18:45:28 GMT

Thank you so much!

Re: Create shift column depending on the time

VahidDM — Wed, 29 Sep 2021 13:59:18 GMT

oops, forgot to add return in the code

try this

Measure = Var _A= time(6,20,0) Var _B = time(14,20,00) Var _C = time(22,20,00) Var _D = time(06,19,59) return IF ( HOUR ( [Hora PO] ) >= _A && HOUR ( [Hora PO] ) <= _B, 1, IF ( HOUR ( [Hora PO] ) > _B && HOUR ( [Hora PO] ) <= _C, 2, 3))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Create shift column depending on the time

pagliaci — Wed, 29 Sep 2021 16:32:40 GMT

It keeps getting the same error, the last column is always returning 3, even though it is morning time (1st column)..

Re: Create shift column depending on the time

VahidDM — Wed, 29 Sep 2021 23:14:17 GMT

pagliaci

Sorry I forgot to remove HOUR in the code, please try this to add your column:

VAR _A = TIME ( 6, 20, 0 ) VAR _B = TIME ( 14, 20, 00 ) VAR _C = TIME ( 22, 20, 00 ) VAR _D = TIME ( 06, 19, 59 ) RETURN IF ( [Hora PO] >= _A && [Hora PO] <= _B, 1, IF ( [Hora PO] > _B && [Hora PO] <= _C, 2, 3 ) )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!