Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |