topic DAX dates between 08:00 and 20:00 in DAX Commands and Tips

DAX dates between 08:00 and 20:00

kvitnitskiy — Thu, 28 May 2020 09:40:41 GMT

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

Re: DAX dates between 08:00 and 20:00

mahoneypat — Thu, 28 May 2020 12:39:25 GMT

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

Re: DAX dates between 08:00 and 20:00

kvitnitskiy — Thu, 28 May 2020 13:04:32 GMT

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

Re: DAX dates between 08:00 and 20:00

mahoneypat — Fri, 29 May 2020 00:09:27 GMT

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"
)