cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eritter Member
Member

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.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

2 REPLIES 2
Highlighted
Super User
Super User

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.
Community Support Team
Community Support Team

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 198 members 2,170 guests
Please welcome our newest community members: