Calculated Column Based on Time

jakoob — Wed, 15 Feb 2023 18:50:35 GMT

Hi,

I'm new to DAX and im having some trouble with creating a calculated colum based off the time stamp of a row.

I have some data that looks like this:

Ticket ID	Create Date/Time
1	1/15/2023 4:36:18 PM
2	1/19/2023 11:00:30 AM
3	1/23/2023 1:12:35 PM
4	1/24/2023 8:41:43 AM
5	1/26/2023 3:17:16 AM

I am trying to make a calculated column that would calculate if a ticket was created either during or outside of business hours (8:30 AM - 5:00 PM). Im trying to get something like the following result:

Ticket ID	Create Date/Time	Created On Hours
1	1/15/2023 4:36:18 AM	No
2	1/19/2023 11:00:30 AM	Yes
3	1/23/2023 1:12:35 PM	Yes
4	1/24/2023 8:41:43 AM	Yes
5	1/26/2023 3:17:16 AM	No

I am using the following DAX statement and all I get are rows stamped with "Yes" even if a rows Date/Time is outside of the standard business hours I defined in the DAX statement.

Created On Hours = IF(AND('Ticket Data'[Date/Time] > TIME(08,29,00), 'Ticket Data'[Date/Time] < TIME(17,00,00)),"Yes","No")

Any help would be appreciated!

Re: Calculated Column Based on Time

tamerj1 — Wed, 15 Feb 2023 18:58:42 GMT

Hi jakoob

Please try

Created On Hours =
IF (
AND (
TIMEVALUE ( 'Ticket Data'[Date/Time] ) > TIME ( 08, 29, 00 ),
TIMEVALUE ( 'Ticket Data'[Date/Time] ) < TIME ( 17, 00, 00 )
),
"Yes",
"No"
)

Re: Calculated Column Based on Time

jakoob — Wed, 15 Feb 2023 19:07:19 GMT

Thank you so much! Works perfectly!

topic Calculated Column Based on Time in DAX Commands and Tips

Calculated Column Based on Time

Re: Calculated Column Based on Time

Re: Calculated Column Based on Time