Forum Discussion
Matching dates to a holiday table
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.
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
2 Replies
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.
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
