Proper handling of deadlines with holidays in DAX

[zttlhsz]

Hello,

I'm looking for an expert who can help me with the following issue.

We have a report for our daily backlog which sets deadlines for our tasks. The deadline depends on the "Country" and "Complexity" values of each tasks, that part of my code works perfectly, my issue is with the exceptions of the holidays, declared in an array.

This is my snippet:

Deadline =
VAR Holidays = { 
        DATE(2024, 12, 24), 
        DATE(2024, 12, 25), 
        DATE(2024, 12, 26)
        }

    VAR Deadline = BaseDate + Matrix

    VAR CorrDeadline =
        IF (
            Deadline IN Holidays,
            Deadline + 1,
            Deadline
        )

    RETURN
    CorrDeadline

BaseDate: fix values from our query (shipment dates)
Matrix: a rule which selects whether 7 or 14 days shall be considered as deadline
Deadline: basically just the base date value and the value of the matrix

I have no idea how to make it "dynamic" to add multiple days to the deadline (if needed), not just one or any manually written numbers. I tried to to create a loop which counts how many holidays are between the base date and the deadline, but the problem with that is the loop won't go further if it finds the number holidays.

Example:

BaseDate: 2024-12-17
Matrix: 7
Deadline: 2024-12-27 (since the 24th, 25th and 26th are holidays)​

If I stick with the loop method, the deadline will be Dec-25 still since between Dec-17 and Dec-24 there's only one holiday in the range, so it still only adds 1 day.

Is there any way to achieve to push the deadline as long as the returned value is not in the range of the holidays?
Any idea is highly appreciated.

Thank you in advance.

Attila

[zttlhsz · Accepted solution]

I came up with a solution eventually (had to use AI of course), enclosed the code:

    VAR TargetRank = 
        SWITCH(
            TRUE(),
            Matrix = 5, 5,
            Matrix = 10, 10,
            0
        )

    VAR WorkdaysAfterBaseDate =
        FILTER(
            'Calendar',
            VALUE('Calendar'[WorkdayFlag]) = 1 &&
            'Calendar'[Date] > BaseDate
        )

    VAR RankedWorkdays =
        ADDCOLUMNS(
            WorkdaysAfterBaseDate,
            "Rank", RANKX(WorkdaysAfterBaseDate, 'Calendar'[Date], , ASC)
        )

    VAR Deadline = 
        CALCULATE(
            FIRSTDATE('Calendar'[Date]),
            FILTER(RankedWorkdays, [Rank] = TargetRank)
        )

    RETURN Deadline

[Anonymous]

Hi @zttlhsz ,

Please refers to the following steps.
The test data is as follows.

The deadline measure is as follows.

Deadline = 
VAR Holidays = { 
    DATE(2024, 12, 24), 
    DATE(2024, 12, 25), 
    DATE(2024, 12, 26)
}

VAR BaseDate = MAX('Table'[Shipment Date]) 
VAR Matrix = 7  
VAR InitialDeadline = BaseDate + Matrix

VAR AdjustedDeadline =
    VAR CheckHoliday = 
        ADDCOLUMNS (
            GENERATESERIES ( 0, 3, 1 ),
            "NewDeadline", InitialDeadline + [Value]
        )
    VAR FilteredHolidays = 
        FILTER (
            CheckHoliday,
            NOT [NewDeadline]  IN Holidays
        )
    RETURN
        MINX ( FilteredHolidays, [NewDeadline] )

RETURN
    AdjustedDeadline

Add this measure to the table visual. The final result is as follows.

Please refers to the attached pbix file for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zttlhsz]

Hi @Anonymous ,

Thank you for your help, unfortunately this is not the solution I'm looking for. It does what I was able to achieve already, I realised I need to re-think this logic.

The problem is, if the calculated deadline is not a holiday, the calculation won't recognize if there were any days which should've been "ignored" in a certain period.

Example: 12-17 (base date) | 14 (matrix) | "deadline" (12-31)
The problem with this is that even tho 12-31 is not a holiday, so the calculation is technically correct, I selected 4 holidays between 12-24 and 12-27 so those are not included in the deadline days.

(Although I wanted to avoid this) I created a Calendar table in my file where I flagged each day whether it is a holiday or a weekend:

Is it possible to reference this table in the Deadline= to create the calculations accordingly?
This is my Matrix:

    VAR Matrix = 
        SWITCH(
            TRUE(),
            'Sharepoint Query'[Country] IN {"ESPT", "DACH"}, "A",
            'Sharepoint Query'[Country] IN {"BNL", "Italy"}, "B",
            'Sharepoint Query'[Country] = "France", 
                IF(
                    'Sharepoint Query'[Complexity Level] IN {"Level 1", "Level 2", "Level 3", "Level 4", "Level 5"},
                    "A",
                    "B"
                ),
            'Sharepoint Query'[Country] = "UKI",
                IF(
                    'Sharepoint Query'[Complexity Level] IN {"Level 1", "Level 2", "Level 5", "Level 6"},
                    "A",
                    "B"
                ),
            0
        )

So what I want to achieve is, if the matrix value is "A", the deadline shall be the 5th non-flagged (means the holiday and weekend values are false) day from the base date, and 10th if the matrix is "B" (and the basedate is the 0 value), looks like this:

Same with the 10 workday version.

Is this managable?

Thanks!