Forum Discussion
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 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
// 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 )
6 Replies
// 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 )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].
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.
So then... I'm confused. What do you need, a calculated column or a measure?
A measure, but if this is not possible then a calculated column.
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.
