Dynamic Holiday Calendar

[jmhoskinson]

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!

[pthapa]

How can I achieve the holiday column for Memorial Day and Labor Day in Dax.

Thank you in advance.

PT.

[Anonymous]

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-tab...

Best Regards,

Mail2inba4

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

[jmhoskinson]

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.

[v-lili6-msft · Accepted solution]

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

[jmhoskinson]

This works wonderfully! I also repurposed your code for the other changing US holidays. Thanks!