Next Working Day - Formula Help

DAX DAX Calculations
1

Hi All

Im having a bit of difficulty with the below problem.

(Backgorund)

SLA 3 Admin Next Working Day (Calculated Column) - Once the completed date has been updated in the system then this will update & generate a calculated column called ‘Completed next working day’ (Date)

THE SLA 3 Admin Next Working Day (Calculated Column) is based on the next working day of the completed next working day i.e. the following, following next day. (if that makes sense)

It has been going well so far in the table - however once it gets to the weekend, it is returning a blank field, which then triggers the SLA to Fail

E24 Issue
E24 Issue1175×246 12.1 KB

SLA 3 Admin NextWorkingDay = 
    VAR NWDAY =
        MINX (
            FILTER (
                MTSE,
                MTSE[Completed Date NextWorkingDay] > EARLIER ( MTSE[Completed Date NextWorkingDay])
                    && **MTSE[*Admin Next Day following Completed Day is A] = "Working Day"**
            ),
            MTSE[Completed Date NextWorkingDay]
        )
        RETURN
        IF ( MTSE[*Admin Next Day following Completed Day is A]= "Weekend", NWDAY, MTSE[Completed Date NextWorkingDay] +1)

I think the issue is related to another calculated column called

Admin Next Day following Completed Day is A

this is how it is setup in the background

e24 issue 2
e24 issue 21570×190 11.7 KB

on day NR 6 - it is set to Weekend

however if i change this to “Working Day”

then the blank day generates a next working day

e24 issue 3
e24 issue 3903×190 10.6 KB

e24 issue 4
e24 issue 41104×203 10.8 KB

ANy help would be greatly appreciated

thanks in advance

2

Hi @martynross85. I see the result of SWITCH 1 is spelled differently (“Workin Day”); perhaps that might improve things a bit?
Greg

2 Likes
3

Hi @Greg,

Good Spot - and that would defo have caused another issue - however still same result.

Further looking into it - it seems it is evaluating the weekend correctly - but the transition from thursday to Friday is the issues - see below

e24 issue 5
e24 issue 51319×513 59.5 KB

4

Hi @martynross85. If possible, can you please provide a work-in-progress PBIX for the forum members to use to further their analysis?
Greg

5

Thanks for posting your question @martynross85. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

6

Hi @martynross85, we’ve noticed that no response has been received from you since the 30th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!