PowerPivot – Public Holidays By State

There are plenty of examples around about how to calculate working days for a single Public Holiday calendar.    But what happens when the Public Holidays vary by state ?   This post walks through a PowerPivot example for calculating a single measure “Working Days” that works when the states each have their own distinct Public Holiday calendar.

For the purposes of this example, a working day is defined as :

a) any day that does not fall on a weekend; and

b) any week day that does not fall on a public holiday

To begin, note we have imported 3 tables into PowerPivot, our Calendar table which is our complete sequential list of dates at day level, our PublicHolidays table which contains the list of public holiday dates per state, and a state table with one row per state.

PublicHolidaysDiagram

In our Calendar table we need a column called WeekDayCount where the value = 1 if the date is a week day, and zero if the day falls on a weekend.  As the Calendar table was prepared as an Excel table, the formula used to calculate WeekDayCount is …


=IF([@Weekend]="No",1,0)

For Public Holidays we import a 4 column table into PowerPivot (Date, State, Holiday Name and Factor).  This table contains only the specific public holiday dates per state, not a full sequential list of dates like our Calendar table.  If you don’t have this data available in a feed, you may need to manually prepare this data yourself.  The good news it doesn’t change that often.   Our 4th column called Factor is set to -1 for all rows in this table.  This will be used in the DAX calculation to establish a working day count.

PublicHolidays3

Finally we add a Working Day Measure in PowerPivot that sums all of the week days in the Calendar table and subtracts any public holiday that falls on a week day.


WorkingDays:=SUMX(Calendar,Calendar[WeekDayCount]) + SUMX(PublicHolidays,RELATED(Calendar[WeekDayCount])*PublicHolidays[Factor])

Finally we can browse our PowerPivot model to see the number of Working Days by State.

PublicHolidays1

If you are interested in counting days between Date Ranges, for example, marketing activities or a school holiday calendar, the following article by Alberto is a good concise read ….

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/12/powerpivot-counting-active-days.aspx

Nanu Nanu !

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s