DAX dates between 08:00 and 20:00

[kvitnitskiy]

Hello all,

 I just started use POwerBI ind found it super nice.

 my question is, hot to create new column with next data:

if datetime  is betwean 08:00 - 20:00 -  return date & 08-20

if datetime is  betvean 20:00-08:00 next day - return date & 20:08

 like in table below. 
  

Check_In_Date		DAX
27.04.2020 9:30		27.04.2020  08-20
27.04.2020 10:14		27.04.2020  08-20
27.04.2020 10:44		27.04.2020  08-20
27.04.2020 15:12		27.04.2020  08-20
27.04.2020 15:31		27.04.2020  08-20
27.04.2020 20:01		27.04.2020  20-08
27.04.2020 21:04		27.04.2020  20-08
27.04.2020 22:03		27.04.2020  20-08
28.04.2020 2:09		27.04.2020  20-08
28.04.2020 7:39		27.04.2020  20-08
28.04.2020 10:18		28.04.2020  08-20
28.04.2020 11:13		28.04.2020  08-20
28.04.2020 11:44		28.04.2020  08-20

[mahoneypat]

Here is a DAX expression that will get your result.  I had to made a DateTime column from your input date, so you may have to do the same first.  I called my DateTime column 'ConvertedDateTime'.

NewDAXColumn =
SWITCH (
TRUE (),
HOUR ( CheckIn[ConvertedDateTime] ) < 8, DAY ( CheckIn[ConvertedDateTime] ) - 1 & "."
& MONTH ( CheckIn[ConvertedDateTime] ) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 20-08",
HOUR ( CheckIn[ConvertedDateTime] ) >= 20, DAY ( CheckIn[ConvertedDateTime] ) & "."
& MONTH ( CheckIn[ConvertedDateTime] ) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 20-08",
DAY ( CheckIn[ConvertedDateTime] ) & "."
& MONTH ( CheckIn[ConvertedDateTime] ) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 08-20"
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[kvitnitskiy]

Thanks,  this is almost i need, except this small issue at the begining  of month.

[mahoneypat · Accepted solution]

Please try this modified one.  I should have done it this way first time.

NewDAXColumn =
SWITCH (
TRUE (),
HOUR ( CheckIn[ConvertedDateTime] ) < 8, DAY ( CheckIn[ConvertedDateTime]-1)  & "."
& MONTH ( CheckIn[ConvertedDateTime] -1) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 20-08",
HOUR ( CheckIn[ConvertedDateTime] ) >= 20, DAY ( CheckIn[ConvertedDateTime] ) & "."
& MONTH ( CheckIn[ConvertedDateTime] ) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 20-08",
DAY ( CheckIn[ConvertedDateTime] ) & "."
& MONTH ( CheckIn[ConvertedDateTime] ) & "."
& YEAR ( CheckIn[ConvertedDateTime] ) & " 08-20"
)