Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Extracting and comparing ranges

Hello, I've spent a ton of hours trying to find an answer to this problem, but there doesn't seem to be any as far as I can tell.

 

My goal is to create a column that identifies if the date is a working day. The issue is that I can't seem to extract the date for the day after Thanksgiving for each year. Here's what I have:

 

I have a date table spanning from 2020-2029.

I have a holiday table that contains a column for the dates of major US holidays and a column labeling those holidays.

 

I was able to get the holidays over to the date table with the lookupvalue function, but I also need to identify the day after thanksgiving. I know I could just manually look up the dates and insert them, but I wanted to see if there was a more automated method to doing this.

 

On a related note, is there a way of comparing a column from the date table to a range of values from a column in the holiday table. Not all of the major holidays are counted as "company holidays" so I really wanted to do something along the lines of:

 

WorkingDay = IF('DateTable'[Holiday] = ("Memorial Day", "Thanksgiving Day", etc), 0, 1)

 

where the red font would be the list of specific holidays. I know I could do this with the switch function or repeat a bunch of ||, but I figured I'd ask you all if there's a shorter alternative.

 

Thanks in advance!

  • what you are looking for is the "IN"  keyword. Note it needs curly bracket, unlike in SQL.

     

    WorkingDay = IF('DateTable'[Holiday] IN {"Memorial Day", "Thanksgiving Day", etc}, 0, 1)

     

    your other question is difficult to answer without seeing the data model.

     

4 Replies