Find Date X days in the Future, Skipping Non-WorkDays

[Brightsider]

So, I'm building a calculation to dynamically calculate whether a given item of work was delivered inside of our Service Level Agreement or not: if the item of work if a First Request, it must be turned around in two business days. If it is a Rework, it must be turned around in one business day.

To find this, I want to take the date the item of work landed on the desk of our employee, and add the one or two days to that date to find the date we must turn the item of work around in to be in SLA. Easy enough, but I need this measure to only count business days, skipping holidays and weekends.

I have a Date table with an Is Workday function pre-calculated, so that's done, but my problem is: how do I find the proper date consistently? Here is my current attempt at it:

ADDCOLUMNS(
	FILTER('Opportunity State History', 'Opportunity State History'[csa_newstatename] = "Proposal Request"),
	"InSLA",
	VAR StartDate = 'Opportunity State History'[csa_newstatedatetime]
	VAR requestSLAgoal = IF('Opportunity State History'[proposalrequesttype] = "Rework", 1, 2)
	VAR SLAdate =
	CONCATENATE(
		CALCULATE(
			MINX(_Dates, _Dates[Date]),
			DATESBETWEEN(_Dates[Date], StartDate + requestSLAGoal, 'Opportunity State History'[csa_newstatedate] + 7),
			_Dates[Is Workday] = TRUE
		),
		" " & TIMEVALUE(StartDate)
	)
	VAR SLAdatetime = 
		DATEVALUE(SLAdate) + TIMEVALUE(SLAdate)
	RETURN
	IF(
			IF(
			ISBLANK('Opportunity State History'[csa_nextstatedate]),
			TODAY() < SLAdatetime,
			'Opportunity State History'[csa_nextstatedate] < SLAdatetime
			),
		True,
		False
	)
)

This kind of works: For each item of work, it generates a list dates between when it was submitted and and week later, filters those dates down to only include Workdays, and then selected the smallest date that is at least the number of days away from the submission date as the SLA turnaround time of 1 or 2 days. The problem is fridays: If a two-day turnaround item is submitted Friday, the smallest date greater than that returns as Monday instead of Tuesday. Which is correct but not what I want. 

To remedy this, I envision a procedure of still generating the list of dates, and then doing a running total of workdays. Something like this:

Date	Is Work Day?	Workday Total
6/15/2024	False	0
6/16/2024	False	0
6/17/2024	True	1
6/18/2024	True	2
6/19/2024	True	3
6/20/2024	True	4

And then just using SELECTCOLUMNS to select the row with a value equal to the turnaround time (For a 2 day turnaound, it would be row 4, 6/18/2024).

But... I don't quite know how to do that. Anythought on how to generate something like that (or if there's just a more efficient way of doing what I'm trying to do?)

[bhanu_gautam · Accepted solution]

@Brightsider , Try using below

ADDCOLUMNS(
FILTER('Opportunity State History', 'Opportunity State History'[csa_newstatename] = "Proposal Request"),
"InSLA",
VAR StartDate = 'Opportunity State History'[csa_newstatedatetime]
VAR requestSLAgoal = IF('Opportunity State History'[proposalrequesttype] = "Rework", 1, 2)
VAR SLAdate =
CALCULATE(
MINX(
FILTER(
ADDCOLUMNS(
_Dates,
"WorkdayTotal",
SUMX(
FILTER(
_Dates,
_Dates[Date] <= EARLIER(_Dates[Date]) && _Dates[Is Workday] = TRUE
),
1
)
),
[WorkdayTotal] = requestSLAgoal
),
_Dates[Date]
),
_Dates[Date] >= StartDate
)
VAR SLAdatetime =
DATEVALUE(SLAdate) + TIMEVALUE(StartDate)
RETURN
IF(
IF(
ISBLANK('Opportunity State History'[csa_nextstatedate]),
TODAY() < SLAdatetime,
'Opportunity State History'[csa_nextstatedate] < SLAdatetime
),
TRUE,
FALSE
)
)

[Brightsider · Accepted solution]

Quick update, I made a more efficient version of the initial solution:

EVALUATE 
ADDCOLUMNS(
	FILTER('Opportunity State History', 'Opportunity State History'[csa_newstatename] = "Proposal Request"),
	"InSLA",
	VAR StartDate = COALESCE('Opportunity State History'[csa_newstatedate], 'Opportunity State History'[csa_newstatedatetime])
	VAR requestSLAgoal = IF('Opportunity State History'[proposalrequesttype] = "Rework", 1, 2)
	VAR WorkingDays =
		SELECTCOLUMNS(
				FILTER(
				ALL(_Dates),
				_Dates[Date] > StartDate &&
				_Dates[Date] <= (StartDate + 7) &&
				_Dates[Is Workday] = TRUE
			),
			[Date]
		)
	VAR SLAdate =
		INDEX(
			requestSLAgoal,
			WorkingDays,
		)
	VAR SLAdatetime = DATEVALUE(SLAdate) + TIMEVALUE(StartDate)
	RETURN
	IF(
		IF(
			ISBLANK('Opportunity State History'[csa_nextstatedate]),
			TODAY() < SLAdatetime,
			'Opportunity State History'[csa_nextstatedate] < SLAdatetime
		),
		TRUE,
		FALSE
	)
)

Using a Variable with the Index function got it to run in under a second.

[v-kongfanf-msft]

Hi @Brightsider ,

Did bhanu_gautam reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists(just like provide test data about your data model).

Best Regards,
Adamk Kong

[bhanu_gautam · Accepted solution]

@Brightsider , Try using below

ADDCOLUMNS(
FILTER('Opportunity State History', 'Opportunity State History'[csa_newstatename] = "Proposal Request"),
"InSLA",
VAR StartDate = 'Opportunity State History'[csa_newstatedatetime]
VAR requestSLAgoal = IF('Opportunity State History'[proposalrequesttype] = "Rework", 1, 2)
VAR SLAdate =
CALCULATE(
MINX(
FILTER(
ADDCOLUMNS(
_Dates,
"WorkdayTotal",
SUMX(
FILTER(
_Dates,
_Dates[Date] <= EARLIER(_Dates[Date]) && _Dates[Is Workday] = TRUE
),
1
)
),
[WorkdayTotal] = requestSLAgoal
),
_Dates[Date]
),
_Dates[Date] >= StartDate
)
VAR SLAdatetime =
DATEVALUE(SLAdate) + TIMEVALUE(StartDate)
RETURN
IF(
IF(
ISBLANK('Opportunity State History'[csa_nextstatedate]),
TODAY() < SLAdatetime,
'Opportunity State History'[csa_nextstatedate] < SLAdatetime
),
TRUE,
FALSE
)
)

[Brightsider]

So, Good News: this solution works! The Bad News, though: it is very slow, taking over ten seconds even on my small ~15,000 row dataset. 

Is there a more efficient way of accomplishing this same task?