column to designate "working Hours"

[bhmiller89]

I have data for help desk tickets and each ticket has a "resolved date" which is date/time format. I also have a invoked calendar function that includes things like DayofWeekName (Monday,etc), DayInWeek (Friday=5, etc) 

I need to find a way to designate whether or not a ticket was resolved within "working hours" which is Monday-Friday 8AM-6PM 

[v-jiascu-msft · Accepted solution]

Hi @bhmiller89,

1. I would suggest you add one column to the calendar table.

isWorkDay =
IF ( WEEKDAY ( [Date], 2 ) IN { 6, 7 }, 0, 1 )

2. Add calculated column like below.

ifWithinWH =
VAR rDate =
    DATEVALUE ( [Resolved Date] )
VAR rTime =
    TIMEVALUE ( [Resolved Date] )
RETURN
    IF (
        rDate
            IN CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[isWorkDay] = 1 ),
        IF ( rTime >= TIME ( 8, 0, 0 ) && rtime <= TIME ( 18, 0, 0 ), 1, 0 ),
        0
    )

Best Regards,

Dale

[alexei7]

Hi @bhmiller89,

I would probably create a time column - you could either create a custom column or split the column into separate "Date" and "Time" columns in the query.

Then i'd base the "working hours" calculation on the time column.

Hope that helps,

Alex