Holiday date pattern

Power BI Data Model
1

Holidays date patterns

Ok, everyone has their holiday table with any number of dates.
Some will maintain this every year while others will store up -
say 100 years of holidays to maintain the table only once every
100 years but what if we can avoid all this?

I’ll be using US holidays for this discussion but this can
work for almost any holiday.

Holidays like New Years are on the same date but not the same day of week
while others like Thanksgiving are on the same day of week but
not the same date.

Either way, each holiday occurs on 1 of 7 days every year so what
if our holiday table could handle this?

For example, Thanksgiving is on the 4th Thursday of every November
which means its 3 weeks from the 1st Thursday which is
Nov 1 - Nov 7 therefore Thanksgiving will always be Nov 22 - Nov 28
and only ONE of those days is a Thursday.

How can we use this information to our advantage? Continuing to work
with Thanksgiving we have

Nov 22
Nov 23
Nov 24
Nov 25
Nov 26
Nov 27
Nov 28

and like we said only one of these days is a Thursday so lets format the
date

Thu,Nov 22
Thu,Nov 23
Thu,Nov 24
Thu,Nov 25
Thu,Nov 26
Thu,Nov 27
Thu,Nov 28

With Power Query this is a simple merge and the year is irrelevant

11/22/2020 Sun
11/23/2020 Mon
11/24/2020 Tue
11/25/2020 Wed
11/26/2020 Thu Thanksgiving
11/27/2020 Fri
11/28/2020 Sat

Lets take 2032

11/22/2032 Mon
11/23/2032 Tue
11/24/2032 Wed
11/25/2032 Thu Thanksgiving
11/26/2032 Fri
11/27/2032 Sat
11/28/2032 Sun

For fixed holidays like New Years, it occurs on the 1st but the day of
week varies so we have

1/1 Mon
1/1 Tue
1/1 Wed
1/1 Thu
1/1 Fri
1/1 Sat
1/1 Sun

We can also add Observed holidays like Fri, 12/31 and Mon, 1/2s

For each holiday we’ll have 7 entries in our table but the advantage
is we only have to maintain it once. If we to compare this with a typical
holiday table with H holidays * Y years, the date pattern would require only
7*H

The reason I thought of this is because I was the one who had to create the
100 year holiday table a few years back. Seriously - not kidding.

1 Like
2

@markperrone,

Thanks for sharing - really interesting approach. Can you please post a PBIX of a holiday table you’ve created this way? Would be helpful to see the full set of applied steps.

Thanks!

  • Brian
3

Thanks for posting your question @markperrone. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hi @markperrone, due to inactivity, we have tagged this post as SOLVED. If you feel the need to activate the post, we suggest creating a new topic to get a better response from the community. Thanks!