Background Color Expression for Holidays

[joshdellapietro]

Hello,

I currently have a pivot table showing me sales by week (image 1). My weeks are calculated using the following field, [Shipping Date.autoCalendar.Week] with the field [Shipping Date] just being MM/DD/YYYY. Now, I also have loaded a field called [Holiday Date] which again is in MM/DD/YYYY format. My goal is for holiday weeks to be highlighted in light gray in my pivot table (image 2).

I thought a simple formula would of work like:

IF([Shipping Date.autoCalendar.Week]=[Holiday Date.autoCalendar.Week],lightgray())

But I am not getting any results from this.

[settu_periasamy]

Hi @joshdellapietro ,

Is that holiday date field is linked to your Shipping Date Calendar table?

[joshdellapietro]

Hi,

No, they are loaded separately. Linking them is an option.

[settu_periasamy]

Ok. Can you just create  a Straight table char with two fields (Shipping date and Holiday Date) and check those date values are matching ?

[joshdellapietro]

Yes, it allowed me to link the two showing that the date values match.

[joshdellapietro · Accepted solution]

Figured it out, this formula worked:

ONLY(IF([Shipping Date.autoCalendar.Week]=[Holiday Date.autoCalendar.Week],lightgray()))