topic Re: Find Date X days in the Future, Skipping Non-WorkDays in DAX Commands and Tips

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

Brightsider — Wed, 11 Sep 2024 23:10:19 GMT

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

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

bhanu_gautam — Thu, 12 Sep 2024 06:41:50 GMT

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

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

Anonymous — Thu, 12 Sep 2024 10:40:49 GMT

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

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

Brightsider — Thu, 12 Sep 2024 15:56:48 GMT

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?

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

Brightsider — Fri, 20 Sep 2024 16:10:28 GMT

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.