How to flag lead form submission date/time as falling inside or outside business hours

[petelanglois]

Hello,

I'm trying to create a calculated column which indicates whether a lead submission form 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 ("true"). If the value falls outside of business hours, I want the new column to read "false". Here's how it would look (my first attempt did not produce correct results - showing the image only as an example).

Business hours are 

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

I'm not sure how to calculate this measure. I imagine I need to use IF statements and filter the date/time by day of week as well as time of day, but I'm stumped. Do I need to add a day of week column to the model and use that to distinguish between business hours on Mon-Friday vs business hours on Sat/Sun? Do I need to build in some recognition of time zone, to ensure that the measure always reads the time in the correct zone (opposed to whichever time zone I happen to be in when using power bi)?

Thank you for your help!

Best,

Pete

[amitchandak]

@petelanglois , Try like

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)

[petelanglois]

Hi @amitchandak , Thanks for your help. I tried your DAX and this resulted in all results reading "outside". See the dax below. Any ideas what's happening?

IsBusinessHoursLead =

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

[CNENFRNL]

Hi, @petelanglois , you might want to try this formula in the calculated column

Col =
VAR __time = TIMEVALUE ( DimLeads[LeadEnteredDateTime] )
VAR __weekday = WEEKDAY ( DimLeads[leadenteredDateTime], 2 )
RETURN
    SWITCH (
        TRUE (),
        __weekday <= 5
            && __time >= TIME ( 8, 0, 0 )
            && __time <= TIME ( 20, 0, 0 ), "inside",
        __weekday > 5
            && __time >= TIME ( 9, 30, 0 )
            && __time <= TIME ( 18, 0, 0 ), "inside",
        "outside"
    )