I answered some forum questions recently that were similar in nature and I thought they would make a good blog article. The scenario is this: there is a table that contains information about when an employee was absent from work like shown below.
The question is “How many working days were they off work giving consideration to both weekends and public holidays?”.
The trick to solving this problem is to create a good quality calendar table and use the calendar table to validate the days off. I explain the process below.
Find a Good Starting Calendar
I decided to resurrect an article with a Power Query Calendar table that I wrote 18 months ago here: https://p3adaptive.com/2015/02/create-a-custom-calendar-in-power-query/
I have simply cut the code from this previous article above and pasted it into a new blank Power Query as the starting point for this post today. If you want to start from the beginning, go back and read the article above first so you learn the steps to get to my starting point. Here is a sample of what this calendar looks like as is. Note the first thing I then did was to apply a filter to remove the year 2015 (which I don’t need).
I then wanted to enhance this calendar so it contained a column that indicates “working days”. In my demo example, a working day is any day Monday to Friday that is not a public holiday. So that implies I needed to do 2 things. First I needed to create a weekday column and secondly I needed to load up the Public Holidays.
Weekday Column
The weekday column was easy. I simply went to the last step in Power Query, added a new custom column, and added code as shown below (remember PQL code is case sensitive).
This added a new column that returns 1 for weekdays and 0 for weekends. The reason I selected 1 and zero is that it is then easy to simply add up the values in this column to work out how many working days there are.
Grab the Public Holidays
The next step is to add the public holidays. Now I didn’t want to have to do this manually, so I went to my trusty friend Google and found the information I needed online (for NSW Australia). Of course you would need to find your own suitable source.
https://www.industrialrelations.nsw.gov.au/oirwww/NSW_public_holidays/NSW_Public_Holidays.page
I created a new query\From Web Page, and Power Query gave me the following
I promoted the first row to headers, and then I had to merge the date and the year columns so I had a full date column I could use. Once I had a text representation of the full date, I was able to convert this merged column into a Date format.
I then un-pivoted the columns to get this.
And finally I selected “Close and Load to”, “only create connection”.
The next step was to join this new Public Holiday table to my Calendar table. This was quite simple – within Power Query I right clicked on the Calendar table query and then selected “Merge”.
I merged the 2 queries on the Date column using a Left Outer Join (the default as shown in 1 below).
Note that there are only 10 rows that match. This is because my Public Holidays are for the years 2016 – 2018, yet my calendar table is designed to show up to today’s date only. Into the future in 2017/18, the query will find additional matches and it will just work.
After merging the 2 queries, I then expanded the new column (shown as 1 below)
I made sure that I only selected the holiday name (2 above) and deselected the “use original column name as prefix” (shown as 3 above).
As you can see above, my calendar at this point has the names of the holidays as a new column. All I had to do was write a new custom column that concatenates the weekday information with the public holiday information to give me a “working day” column.
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Note how I used spacing, line breaks and brackets in the formula window below to make the code more readable. It will work without doing this, but good luck trying to write and debug the formulas 🙂
As you can see below, this new column returns the 1 from the Weekday column except if there is a public holiday, then it returns 0.
The last step was to change the data types of these columns to be “whole number” rather than “Any”. Once I had loaded these 2 tables into Power Pivot, I had this.
Importantly, I have not joined the 2 tables with a relationship. A relationship can’t work in this scenario because there is a range of dates in the Absence Table (from date, to date) rather than a list of dates. Given there is no relationship, it is necessary to write a DAX formula that looks at the Absence Table then filters the Calendar table so that only the days between the First Day Off and the Return Date are showing. Then add up the Working Day column to work out how many days off.
Once I placed the Days Away formula in a Pivot Table, it looks like shown below, and it correctly handles the weekends and public holidays.
Here is the formula I wrote – it looks complex but let me explain how it works.
- Calculate always executes the filter portion of the formula first (lines 4 through 8 in this case).
- The FILTER function is an iterator. It iterates over the Calendar table (line 5) one row at a time. There are no filters on the Calendar table, so there is no need for an ALL(Calendar) in this case. As FILTER iterates over the table it checks each row in the calendar table to see if the date of the current row in the Calendar table is on or after the MAX of the first day off (line 6) and also if the date is before the return date (line 7). If both of these things are true, then FILTER keeps the date in the filtered copy of the calendar table.
- After iterating the entire calendar, FILTER returns a filtered copy of the Calendar table that contains just the rows needed for the calculation. Some of these days will be working days (indicated with a 1) and some will be weekends or public holidays (indicated with a 0).
- The last step is for CALCULATE to add up this “working days” column (as currently filtered) to work out how many working days the employee was away from work.
Here is my workbook if you would like to download it and take a look (Excel 2016 format). working-days-off-work
