Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])
)
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.
Create a Holidays table, use EXCEPT.
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])
)
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.
@Anonymous my bad, it should be ALL('Holidays'[Date])
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])
)
@Greg_DecklerGreg, my man! Now that I see it and how it works, it makes sense. Thanks for all you do in the community.
My apologies for all of the syntax errors! That's what happens when I try to write DAX without testing it!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |