Reply
Regular Visitor
Posts: 43
Registered: ‎05-30-2018
Accepted Solution

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.

 

 

 

 

 


Accepted Solutions
Highlighted
Community Support Team
Posts: 2,492
Registered: ‎03-15-2018

Re: Matching dates to a holiday table

Hi @eritter

“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?

1.png2.png

3.png

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

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

4.png

Column = LOOKUPVALUE(Holidays[Holidays],'Cases'[CreatedDate],[CreatedDate])
flag = IF([Column]=[CreatedDate],"true",BLANK())

Best Regards

Maggie

View solution in original post


All Replies
Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: Matching dates to a holiday table

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. 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Community Support Team
Posts: 2,492
Registered: ‎03-15-2018

Re: Matching dates to a holiday table

Hi @eritter

“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?

1.png2.png

3.png

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

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

4.png

Column = LOOKUPVALUE(Holidays[Holidays],'Cases'[CreatedDate],[CreatedDate])
flag = IF([Column]=[CreatedDate],"true",BLANK())

Best Regards

Maggie