Forum Discussion
Dynamic Holiday Calendar
I'm trying to create a calendar that automatically marks US holidays (without hard coding the dates in or having a holiday lookup table). I've created the date table with the CALENDARAUTO function, and I have no problem marking the static holidays (New Years, Christmas, Independence Day).
I can't get the changing holidays to work (Thanksgiving, Labor Day, etc.). The big problem is counting occurences of a day of the week in the month (Thanksgiving is the 4th Thursday of November). I know some kind of running total function should do the trick, but I cannot get it to work as a measure or calculated column.
Any help would be most appreciated!
hi jmhoskinson
Just try this logic to create a column
Holiday = var num=IF('Date'[Month]=11&&'Date'[Weekday]=4,RANKX(FILTER('Date','Date'[YearMonth]=EARLIER('Date'[YearMonth])&&'Date'[Weekday]=4),'Date'[Date],,ASC,Skip)) return IF('Date'[Month]=11&&num=4,"thanksgiving day")Result:
Of cource, you need use WEEKDAY Function and other data function to define Weekday/YearMonth in advance.
and here is sample pbix file, please try it.
Regards,
Lin
5 Replies
Hi jmhoskinson ,
We can't acheive this without having holiday lookup table.
You could vote for idea like this
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39090790-holidays-in-calendar-tableBest Regards,
Mail2inba4
If this post helps, then please consider Accept it as the solution to help the other members find easily.
So is there no way to count what Thursday of the month a date is in DAX? That's really the core of my question.
hi jmhoskinson
Just try this logic to create a column
Holiday = var num=IF('Date'[Month]=11&&'Date'[Weekday]=4,RANKX(FILTER('Date','Date'[YearMonth]=EARLIER('Date'[YearMonth])&&'Date'[Weekday]=4),'Date'[Date],,ASC,Skip)) return IF('Date'[Month]=11&&num=4,"thanksgiving day")Result:
Of cource, you need use WEEKDAY Function and other data function to define Weekday/YearMonth in advance.
and here is sample pbix file, please try it.
Regards,
Lin
How can I achieve the holiday column for Memorial Day and Labor Day in Dax.
Thank you in advance.
PT.
