Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Edit DAX to filter out specifics dates from column

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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])
)

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.