Forum Discussion
NOT IN function for holidays
Hello folks,
I have a very simple calendar table which I want to enrich with holiday information in the "WorkingDay" column. For this I have pulled in a table from the internet which has the public holiday information, containing a date column and a description column. It looks so easy but I can't get it to work.
I want to have the boolean expression in "WorkingDay" taking the holidays into account.
Any ideas?
I thought adding a variable of the holiday dates and calling this in a NOT [Date] in {holidays} function would do it, but no...
Calendar =
GENERATE(
CALENDARAUTO(),
var YYYY =
YEAR( [Date] )
var MMM =
MONTH( [Date] )
var Holidays =
{DISTINCT(Feiertage[Column2])}
return
ROW(
"Year", YYYY,
"Month", FORMAT( [Date] , "mmmm" ),
"Month No", MMM,
"Weekday", FORMAT( [Date], "dddd"),
"Weekday No", WEEKDAY( [Date], 2),
"WorkingDay", NOT (
WEEKDAY( [Date], 2) in {6,7}
)
)
)
hashtag_pete How about:
Calendar = GENERATE( CALENDARAUTO(), var YYYY = YEAR( [Date] ) var MMM = MONTH( [Date] ) var Holidays = {DISTINCT(Feiertage[Column2])} return ROW( "Year", YYYY, "Month", FORMAT( [Date] , "mmmm" ), "Month No", MMM, "Weekday", FORMAT( [Date], "dddd"), "Weekday No", WEEKDAY( [Date], 2), "WorkingDay", NOT ( WEEKDAY( [Date], 2) in {6,7} ) && NOT(CONTAINS(Holidays,[Column2],[Date])) ) )hashtag_pete Well, I actually think that this is the better solution (below). I think that it is working because it ends up referencing a column in an actual table, [Column2] because the Holidays variable would have a column name of [Value] and not [Column2]. So probably a bit of luck. However, if you do it this way is better:
Calendar = VAR Holidays = DISTINCT(Feiertage[Column2]) RETURN GENERATE( CALENDARAUTO(), var YYYY = YEAR( [Date] ) var MMM = MONTH( [Date] ) return ROW( "Year", YYYY, "Month", FORMAT( [Date] , "mmmm" ), "Month No", MMM, "Weekday", FORMAT( [Date], "dddd"), "Weekday No", WEEKDAY( [Date], 2), "WorkingDay", NOT ( WEEKDAY( [Date], 2) in {6,7} ) && NOT([Date] in Holidays) ) )
5 Replies
hashtag_pete Maybe:
Calendar = GENERATE( CALENDARAUTO(), var YYYY = YEAR( [Date] ) var MMM = MONTH( [Date] ) var Holidays = {DISTINCT(Feiertage[Column2])} return ROW( "Year", YYYY, "Month", FORMAT( [Date] , "mmmm" ), "Month No", MMM, "Weekday", FORMAT( [Date], "dddd"), "Weekday No", WEEKDAY( [Date], 2), "WorkingDay", NOT ( WEEKDAY( [Date], 2) in {6,7} ) && NOT([Date] in Holidays) ) )I gave this a try but received this error:
<pi>A table of multiple values was supplied where a single value was expected.</pi>I even tried varing in making the holidays a list, but it doesn't seem to matter if I set the {} in the variable or in the function...
hashtag_pete How about:
Calendar = GENERATE( CALENDARAUTO(), var YYYY = YEAR( [Date] ) var MMM = MONTH( [Date] ) var Holidays = {DISTINCT(Feiertage[Column2])} return ROW( "Year", YYYY, "Month", FORMAT( [Date] , "mmmm" ), "Month No", MMM, "Weekday", FORMAT( [Date], "dddd"), "Weekday No", WEEKDAY( [Date], 2), "WorkingDay", NOT ( WEEKDAY( [Date], 2) in {6,7} ) && NOT(CONTAINS(Holidays,[Column2],[Date])) ) )
