Forum Discussion
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...
// 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 )
// 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.