Exclude Weekend from Dynamic Subtraction

[TimmK]

For simplicity I have a table with the three columns "Order Key", "Date" and "Days".

I would like to use a DAX measure to subtract the days from the date for each row. For instance, if date = 2022-11-16 (WED) and days = 2 then the result should be 2022-11-14 (MON).

As for the production only workdays matter, the weekend should be generally excluded. For example:

• Date = 2022-11-16 (WED); Days = 3 then the result should be 2022-11-11 (FRI)
• Date = 2022-11-21 (MON); Days = 3 then the result should be 2022-11-16 (WED)

How can I do this?

I tried the following measure, but it does not work fully correctly:

Adjusted date =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR ReferenceDays =
    SELECTEDVALUE ( 'Table'[Days] )
VAR BaseDate = ReferenceDate - ReferenceDays
VAR WorkingDaysDiff =
    NETWORKDAYS ( BaseDate, ReferenceDate )
VAR Result = BaseDate - ( ReferenceDays - WorkingDaysDiff )
RETURN
    Result

[daXtreme · Accepted solution]

// Run this in DAX Studio to see how it works.

define table TestTable =
    selectcolumns(
        {
            (1, dt"2022-12-01", 5),
            (2, dt"2022-12-05", 3),
            (3, dt"2022-12-01", 3),
            (4, dt"2022-12-03", 2),
            (1, dt"2022-12-10", 5)
        },
        "OrderKey", [Value1],
        "Date", [Value2],
        "Day", format( [Value2], "dddd" ),
        "Days", [Value3]
    )
EVALUATE
    ADDCOLUMNS(
        TestTable,
        "@DateWithDaysSubtracted",
            // Please make sure that the number of days to
            // go back is not more than 50. If it is, this
            // code must be adjusted. This is the code for
            // the calculated column.
            var CurrentDate = TestTable[Date]
            var DaysToSubtract = TestTable[Days]
            var AuxiliaryDateTableWithoutWeekeds =
                SELECTCOLUMNS(
                    FILTER(
                        CALENDAR( CurrentDate - DaysToSubtract - 50, CurrentDate ),
                        WEEKDAY( [Date], 2 ) IN {1, 2, 3, 4, 5}
                    ),
                    "@CalendarDate", [Date]
                )
            var DatesWithRanks =
                ADDCOLUMNS(
                    AuxiliaryDateTableWithoutWeekeds,
                    "@Rank",
                        var RunningDate = [@CalendarDate]
                        var Ranking =
                            RANKX(
                                AuxiliaryDateTableWithoutWeekeds,
                                [@CalendarDate],
                                RunningDate,
                                DESC
                            ) - 1 // so that the ranks start with 0
                        return
                            Ranking
                )
            var Result =
                MAXX(
                    Filter(
                        DatesWithRanks,
                        [@Rank] = DaysToSubtract
                    ),
                    [@CalendarDate]
                )
            return
                Result
    )

[TimmK]

I created a new table and inserted the following adapted code:

ADDCOLUMNS(
        Screen,
        "@DateWithDaysSubtracted",
            // Please make sure that the number of days to
            // go back is not more than 50. If it is, this
            // code must be adjusted. This is the code for
            // the calculated column.
            var CurrentDate = Screen[Date]
            var DaysToSubtract = Screen[Days]
            var AuxiliaryDateTableWithoutWeekeds =
                SELECTCOLUMNS(
                    FILTER(
                        CALENDAR( CurrentDate - DaysToSubtract - 50, CurrentDate ),
                        WEEKDAY( [Date], 2 ) IN {1, 2, 3, 4, 5}
                    ),
                    "@CalendarDate", [Date]
                )
            var DatesWithRanks =
                ADDCOLUMNS(
                    AuxiliaryDateTableWithoutWeekeds,
                    "@Rank",
                        var RunningDate = [@CalendarDate]
                        var Ranking =
                            RANKX(
                                AuxiliaryDateTableWithoutWeekeds,
                                [@CalendarDate],
                                RunningDate,
                                DESC
                            ) - 1 // so that the ranks start with 0
                        return
                            Ranking
                )
            var Result =
                MAXX(
                    Filter(
                        DatesWithRanks,
                        [@Rank] = DaysToSubtract
                    ),
                    [@CalendarDate]
                )
            return
                Result
    )

However, I unfortunately get the error message "The start date or end date in Calendar function can not be Blank value.".

My original table is "Screen" that includes the columns [OrderKey], [Date], [Day] and [Days].

[daXtreme]

Well, the error message is clear. This line

CALENDAR( CurrentDate - DaysToSubtract - 50, CurrentDate ),

apparently receives something that's not allowed. Investigate this in DAX Studio and fix it. Something is probably wrong with CurrentDate which is set by your code to BLANK. Sorry, I can't help you with this as I don't have your data. You have to troubleshoot by yourself.

[daXtreme]

So then... I'm confused. What do you need, a calculated column or a measure?

[TimmK]

A measure, but if this is not possible then a calculated column.

[daXtreme]

If the number of days to go back is set in stone for any single value of the date, then creating a calculated column makes more sense because this is not a dynamic calculation. In any case, once you've got this calc column, you can create a measure based on it.