Hello Team,
I’m currently facing challenges in Power Query while trying to calculate the difference in days between two dates, considering only working days, working hours, and excluding holidays.
Although I have a DAX-based solution that works as expected in terms of logic, it has performance issues. When attempting the same in Power Query, I'm encountering errors, especially for tickets created outside of working hours.
I would appreciate your help in resolving this.
Please find below my DAX and Power Query scripts for reference.
Thank you.
Dax
Days_Difference Exclude weekends =
VAR SubmittedDateTime = 'tbl_Service Request'[SR_CREATED]
VAR ResolvedDateTime = COALESCE( 'tbl_Service Request'[SR_CLOSE_DATE], NOW())
-- Define working hours
VAR WorkingStartTime = TIME(8, 0, 0) -- 8:00 AM
VAR WorkingEndTime = TIME(17, 0, 0) -- 5:00 PM
VAR WorkingHoursPerDay = 9
VAR StartHour = 8
VAR EndHour = 17
-- Adjust start time (if after working hours, move to next day)
VAR AdjustedStartTime =
IF(
TIME(HOUR(SubmittedDateTime), MINUTE(SubmittedDateTime), SECOND(SubmittedDateTime)) > WorkingEndTime,
DATEVALUE(SubmittedDateTime) + 1 + WorkingStartTime,
MAX(SubmittedDateTime, DATEVALUE(SubmittedDateTime) + WorkingStartTime)
)
-- Adjust end time (if after working hours, move to end of working day)
VAR AdjustedEndTime =
IF(
TIME(HOUR(ResolvedDateTime), MINUTE(ResolvedDateTime), SECOND(ResolvedDateTime)) > WorkingEndTime,
DATEVALUE(ResolvedDateTime) + WorkingEndTime,
ResolvedDateTime
)
-- Define start and end dates and times for calculations
VAR StartDate = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime))
VAR StartTime = TIME(HOUR(AdjustedStartTime), MINUTE(AdjustedStartTime), SECOND(AdjustedStartTime))
VAR EndDate = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime))
VAR EndTime = TIME(HOUR(AdjustedEndTime), MINUTE(AdjustedEndTime), SECOND(AdjustedEndTime))
-- If the ticket is within the same working day, calculate the difference based on start and end times
VAR SameDayWorkingHours =
IF(
StartDate = EndDate &&
RELATED('DimDate'[IsWorkingDay]) = TRUE(),
MAX(0, DATEDIFF(AdjustedStartTime, AdjustedEndTime, SECOND)) / 3600,
0
)
-- Calculate the number of seconds for the start day and end day if it's not the same day
VAR StartDaySeconds =
IF(
COUNTROWS(
FILTER(
'DimDate',
'DimDate'[CalendarDate] = StartDate &&
'DimDate'[IsWorkingDay] = TRUE()
)
) > 0,
MAX(0, DATEDIFF(AdjustedStartTime, DATEVALUE(AdjustedStartTime) + WorkingEndTime, SECOND)),
0
)
VAR EndDaySeconds =
IF(
COUNTROWS(
FILTER(
'DimDate',
'DimDate'[CalendarDate] = EndDate &&
'DimDate'[IsWorkingDay] = TRUE()
)
) > 0,
MAX(0, DATEDIFF(DATEVALUE(AdjustedEndTime) + WorkingStartTime, AdjustedEndTime, SECOND)),
0
)
-- Calculate the number of full working days between start and end (exclusive)
VAR FullWorkingDays =
COUNTROWS(
FILTER(
'DimDate',
'DimDate'[CalendarDate] > StartDate &&
'DimDate'[CalendarDate] < EndDate &&
'DimDate'[IsWorkingDay] = TRUE()
)
)
-- Total working hours from full days
VAR FullDaysHours = FullWorkingDays * WorkingHoursPerDay
-- Total working hours
VAR TotalWorkingHours =
IF(SameDayWorkingHours > 0, SameDayWorkingHours, (StartDaySeconds + EndDaySeconds) / 3600 + FullDaysHours)
RETURN
CEILING(TotalWorkingHours/WorkingHoursPerDay,1)
Power Query M Code
let
// Load data
Source = Sql.Database("RV2IVWPBI1P\RAYAMSSQLSERVER", "RayaOperationsData"),
SR = Source{[Schema="dbo", Item="Fact_Siebel_ServiceRequest"]}[Data],
// Remove unwanted columns
RemovedCols = Table.RemoveColumns(SR, {
"SR_TYPE", "SUB_AREA", "PRIORITY", "REQUESTOR_NAME_EN", "SEVERITY",
"SR_DUE_DATE", "TOTAL_TIME", "VEHICLE_MAKE", "VEHICLE_MODEL", "MODEL_YEAR",
"PENDING_PAYMENT_START", "PENDING_PAYMENT_END", "LAST_UPD", "dw_created", "dw_last_upd"
}),
ChangedTypes = Table.TransformColumnTypes(RemovedCols, {
{"SR_CREATED", type datetime},
{"SR_CLOSE_DATE", type datetime}
}),
// Define working hours
WorkingStart = #time(8, 0, 0),
WorkingEnd = #time(17, 0, 0),
// 🟨 Holiday List
HolidayList = {
#date(2025, 3, 30), #date(2025, 3, 31), #date(2025, 4, 1), #date(2025, 4, 2),
#date(2025, 6, 5), #date(2025, 6, 8), #date(2025, 6, 9), #date(2025, 6, 10)
},
// ✅ Adjust Created Date
AdjustedStart = Table.AddColumn(ChangedTypes, "AdjustedStart", each
let
dt = [SR_CREATED],
defaultTime = #time(8, 0, 0),
safeDT = if dt = null then DateTime.LocalNow() else dt,
t = Time.From(safeDT),
d = Date.From(safeDT),
dow = Date.DayOfWeek(d, Day.Sunday),
nextWorkDate =
List.First(
List.Select(
List.Dates(d + 1, 7, #duration(1,0,0,0)),
each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidayList, _)
)
),
adjusted =
if dow > 4 or List.Contains(HolidayList, d) then
DateTime.From(nextWorkDate + defaultTime)
else if t < defaultTime then
DateTime.From(d + defaultTime)
else if t >= WorkingEnd then
DateTime.From(nextWorkDate + defaultTime)
else safeDT
in
adjusted
, type datetime),
// ✅ Adjust Closed Date
AdjustedEnd = Table.AddColumn(AdjustedStart, "AdjustedEnd", each
let
dt = if [SR_CLOSE_DATE] = null then DateTime.LocalNow() else [SR_CLOSE_DATE],
t = Time.From(dt),
d = Date.From(dt),
adjusted =
if t < WorkingStart then
DateTime.From(d + WorkingStart)
else if t > WorkingEnd then
DateTime.From(d + WorkingEnd)
else dt
in
adjusted
, type datetime),
// Date Range
AddDateRange = Table.AddColumn(AdjustedEnd, "DateRange", each
let
s = Date.From([AdjustedStart]),
e = Date.From([AdjustedEnd])
in
List.Dates(s, Duration.Days(e - s) + 1, #duration(1, 0, 0, 0))
, type list),
// Working Days (Sunday–Thursday, excluding holidays)
WorkingDates = Table.AddColumn(AddDateRange, "WorkingDates", each
List.Select([DateRange], each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidayList, _))
, type list),
// Calculate Working Hours
WorkingHours = Table.AddColumn(WorkingDates, "WorkingHours", each
let
startDT = [AdjustedStart],
endDT = [AdjustedEnd],
startDate = Date.From(startDT),
endDate = Date.From(endDT),
dates = [WorkingDates],
hours = List.Sum(
List.Transform(dates, each
let
currentDate = _,
startTime = if currentDate = startDate then Time.From(startDT) else WorkingStart,
endTime = if currentDate = endDate then Time.From(endDT) else WorkingEnd,
validStart = if startTime < WorkingStart then WorkingStart else startTime,
validEnd = if endTime > WorkingEnd then WorkingEnd else endTime,
duration = Duration.TotalHours(validEnd - validStart)
in
if validEnd > validStart then duration else 0
)
)
in
if startDT = null or endDT = null then null else Number.Round(hours, 2)
, type number),
// Calculate SLA Working Days (9 hours per day rule)
SLA_WorkingDays = Table.AddColumn(WorkingHours, "SLA_WorkingDays", each
let
wh = [WorkingHours]
in
if wh = null then null
else if wh < 9 then 0
else Number.RoundUp(wh / 9)
, type number),
Cleanup = Table.RemoveColumns(SLA_WorkingDays, {"DateRange", "WorkingDates"})
in
Cleanup
