Exclude Holidays as a Slicer by Market

[v-fehum]

Hello everyone,

I have a bar and line chart with day number on the x-axis and units sold on the y-axis. The line is a measure for how we are pacing towards the end of the month.

I need to add in holidays, by market, so I can exclude these days from the visual. 

I have an existing date, market, and holiday table with a 1:many relationship but this only filters by holiday dates and not market.

The holidays are by market, e.g. there is a row for Christmas for CA (Canada) and the US. Can anyone provide some tips on how to accomplish this?

[v-frfei-msft · Accepted solution]

Hi @v-fehum ,

One sample for your reference, Please check the following steps as below. If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

1. Create a calculated column in holiday table.

Month day = MONTH('Holiday'[Date])*100+DAY('Holiday'[Date])

2. Create a measure as below to get the excepted result.

Measure = 
CALCULATE (
    SUM ( 'Table'[Sold] ),
    FILTER (
        'Table',
        (
            MONTH ( 'Table'[Date] ) * 100
                + DAY ( 'Table'[Date] )
        )
            IN VALUES ( Holiday[Month day] )
            = FALSE ()
    )
)

 

Pbix as attached.