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.
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 …
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.
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.
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 ….
Nanu Nanu !