How to create a flag column indicating if a lead falls inside our outside business hours

DAX DAX Calculations
1

Hello,

I’m trying to create a calculated column which indicates whether a website lead submission arrived inside or outside business hours. This new column would reference an existing date/time column. If the date/time value falls within business hours, I want a new calculated column to read (“inside”). If the value falls outside of business hours, I want the new column to read “outside”.

Business hours are

  • M-F: 8 am - 8pm CST
  • Sat/Sun: 9:30 am - 6pm CST

Here’s what I have so far:

New Column =
var _time = DimLeads[LeadEnteredDateTime]
var _day = weekday(DimLeads[LeadEnteredDateTime], 2)

return
switch(True() ,
_day <= 5 && _time >= time(8,0,0) && _time <= time(20,0,0) , “inside”,
_day > 5 && _time >= time (9,30,0) && _time <= time(18,0,0), “inside”, “outside” )

For some reason, no matter what the date/time, the result is always “outside”. Any idea what’s happening here?

Thanks!
Pete

2

I think .time is missing
new Col =
var _time = [leadenteredDateTime].time
var _day = weekday([leadenteredDateTime],2)
switch(True() ,
_day <= 5 && _time>=time(8,0,0) && && _time<=time(20,0,0) , 1 ,
_day > 5 && _time>=time(9,30,0) && && _time<=time(18,0,0),1,
0)

1 Like
3

Hi @pete.langlois

Please try using below code.

New Column = 
var _time = TIME( HOUR('DimLeads'[LeadEnteredDateTime]),MINUTE(DimLeads[LeadEnteredDateTime]),SECOND(DimLeads[LeadEnteredDateTime]))
var _day = WEEKDAY('DimLeads'[LeadEnteredDateTime],2)
RETURN
SWITCH ( 
    True(),
    _day  <= 5 && _time >= time(8,0,0) && _time <= time(20,0,0) , "inside",
    _day > 5 && _time >= time (9,30,0) && _time <= time(18,0,0), "inside", 
    "outside"
)

Below is screen shot FYR.

image
image1314×732 35 KB

2 Likes
4

Nailed it. Thanks, @MK3010!