Forum Discussion

TimmK's avatar
TimmK
Helper IV
3 years ago
Solved

Exclude Weekend from Dynamic Subtraction

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 d...
  • daXtreme's avatar
    daXtreme
    3 years ago
    // 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's avatar
TimmK
Helper IV
3 years ago

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's avatar
daXtreme
Solution Sage
3 years ago

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.