topic Re: Exclude Weekend from Dynamic Subtraction in DAX Commands and Tips

Exclude Weekend from Dynamic Subtraction

TimmK — Tue, 15 Nov 2022 10:09:51 GMT

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

Re: Exclude Weekend from Dynamic Subtraction

daXtreme — Tue, 15 Nov 2022 13:45:11 GMT

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

Re: Exclude Weekend from Dynamic Subtraction

TimmK — Tue, 15 Nov 2022 13:48:42 GMT

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

Re: Exclude Weekend from Dynamic Subtraction

daXtreme — Tue, 15 Nov 2022 14:39:35 GMT

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

Re: Exclude Weekend from Dynamic Subtraction

daXtreme — Wed, 16 Nov 2022 13:36:26 GMT

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.

Re: Exclude Weekend from Dynamic Subtraction

TimmK — Wed, 16 Nov 2022 11:05:21 GMT

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].

Re: Exclude Weekend from Dynamic Subtraction

daXtreme — Wed, 16 Nov 2022 13:35:22 GMT

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.