Edit DAX to filter out specifics dates from column

[Anonymous]

Hi,

I have some DAX that is filtering out weekends. But I also need it to filter out specifcs company holidays. He is the DAX i'm currently using. I need the dates removed from the date column.

dim date = 
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
        "WeekDay", WEEKDAY ( [Date], 2 )
    ),
    [WeekDay] <= 5
)

Here is a picture of my two columns. I need to remove Christmas, Thanksgiving and so on. Any help is appreciated.

[v-jayw-msft]

Hi @Anonymous ,

You will need a Holiday table. Refer the topic below about how to create Holiday table.

https://community.powerbi.com/t5/Desktop/Public-Holiday-Script/m-p/178341.

Mark the holidays and weekends then filter the tag in your formula.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Create a Holidays table, use EXCEPT.

[Anonymous]

Hi @Greg_Deckler,
Thanks for replying. I do have a holidays table. And whenever I try to add anything to that existing dax it breaks it. Where within that dax would I put EXCEPT?

[Greg_Deckler]

Should be something along the lines of:

dim date = 
EXCEPT(
  FILTER (
      ADDCOLUMNS (
          CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
          "WeekDay", WEEKDAY ( [Date], 2 )
      ),
      [WeekDay] <= 5
  ),
  ALL('Holidays',[Date])
)

[Anonymous]

Hi @Greg_Deckler ,

Thanks for reaching out so quickly. Looks like I'm still having an issue.Here are some screen shots of what's going on. I'm a bit confused. I tried two different ways. I hvae included a screen shot of the holiday table. Maybe the dim date table isn't right or something.

[Greg_Deckler]

@Anonymous  my bad, it should be ALL('Holidays'[Date])

[Anonymous]

@Greg_DecklerI did that example in the first picture I sent and got an error.

[Greg_Deckler · Accepted solution]

Sorry, you need a SELECTCOLUMNS in there:

dim date = 
EXCEPT(
  SELECTCOLUMNS(
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
            "WeekDay", WEEKDAY ( [Date], 2 )
        ),
        [WeekDay] <= 5
    ),
    "Date",[Date]
  ),
  ALL('Holidays',[Date])
)

[Anonymous]

@Greg_DecklerGreg, my man! Now that I see it and how it works, it makes sense. Thanks for all you do in the community.

[Greg_Deckler]

My apologies for all of the syntax errors! That's what happens when I try to write DAX without testing it!!