Matching dates to a holiday table

[Anonymous]

I have a column that contains dates that cases were opened - 'Cases'[CreatedDate] with a date/time format of 12/26/2016 5:56:17 PM

In a second table I have all corporate holidays listed - 'Holidays'[Holiday Date] with a date format of 12/16/2016

I want to build a calculated column that identifies matching dates and produces a result of "True" in the column if the CreatedDate matches a Holiday Date. 

I built a relationship between the values and was able to get a column to show the date, but can't figure out how to build the matching output I'm trying to get.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

“I built a relationship between the values and was able to get a column to show the date”

Do you mean “built a relationship between the values” instead of building a relationship on the date column?

Does the following example do as you said?

Then with a DAX, I can lookup “Holiday” based on “value” as follows.

Next, identify matching dates and produce a result of "True"

Column = LOOKUPVALUE(Holidays[Holidays],'Cases'[CreatedDate],[CreatedDate])

flag = IF([Column]=[CreatedDate],"true",BLANK())

Best Regards

Maggie

[MattAllington]

There is a lot to learn and do. 

Remove the time portion from your column

create a calendar table. https://exceleratorbi.com.au/power-pivot-calendar-tables/

Join both current tables to be calendar table, then use the calendar table date column and not the other date columns https://exceleratorbi.com.au/relationships-power-bi-power-pivot/

dont Write a calculate column https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

depending on what you are trying to do, you could then creat a matrix using calendar date on rows, then pull in the count of cases and the holiday flag.