# Perpetual Holiday Calendar – POTW #12 (Power Query Solution)

In this blog post, we’ll explore my solution to Problem of the Week #12. For this challenge, we revisited Enterprise DNA’s maiden Problem of the Week challenge, where we created a perpetual holiday calendar from a mix of specific and relative dates. But this time around, we will be using only Power Query. You can watch the full video of this tutorial at the bottom of this blog.

If you’re stuck on the problem breakdown itself, you can revisit the first iteration to see if there are elements in any of the approaches that can help you with this challenge.

While reading the submissions, I’ve seen a mix of people leveraging the user interface and writing custom functions to create a perpetual calendar. It’s been enjoyable to analyze each entry, and if you want to get inspired, I encourage you to do the same.

When I was working through this scenario myself, I ended up creating multiple solutions. The one I went with best illustrates how you can not only break down a problem into smaller pieces but also make them visible. This is very helpful if you encounter an error or unexpected results.

## The Starting Point For Creating A Perpetual Calendar

This raw holiday table is our starting point. To piece together a date, we need a year, a month, and a day for specific dates. All that’s missing is the year.

But for relative dates, we need to do a little bit more work. For starters, we need to split that day column into both the numeral and the weekday. Let’s review those steps.

## Retrieving The Years From The Dates Table

To make this solution dynamic, I’m going to retrieve the years from the dates table. In the formula bar, you can see that I’ve used a projection to get a single column table from my date table. This, of course, will contain duplicates. To remove those, I’ve added Table.Distinct.

When I click off to the side in the white space beside any of these tables, you’ll also see a list with the distinct years present in my date table query.

We were given a month name, but we will actually need a month number. Now, when we concatenate a year to a month name, we can use the Date.From function to return the first of that month as a date value.

By adding Date.Month, we’re left with just the month number for that specific date. For January, it returns a 1. For February, it returns a 2, and so on.

## Getting The Numeral From The Day Column

To get the numeral from the day column, we decided not to split the column and extract the value instead. As you can see in the formula bar, we used Text.BeforeDelimiter and used that space to identify the right and left part of that text string.

If there’s no space, we want to return whatever remains in the day column. So for the first record, that returns a 1. For the second record that contains that space, it returns the first part of the text string, the word Third.

We will use a similar approach to get the date name, but use the function List.PositionOf. This returns the zero-based offset of the value in the list passed as first argument, and it returns -1 if the value doesn’t appear in that list.

For the first record, it passes the value 1, which isn’t present in our list. This is why it returns the value of -1. For the second record, it returns the value Monday.

It passes that to our function and returns a 1 because it’s the second item in our list. Next, we’ll replace all -1 values with a null.

At this point, we have created all of the building blocks we need to invoke the custom function.

## Invoking The Custom Function For The Perpetual Holiday Calendar

In the formula bar, we can see that the function takes four arguments: a year, a month, a numeral, and a weekday.

But how does it determine that date value? We’ll use the List.PositionOf again to get a numeric value for the numeral. We have a list containing first, second, third, and fourth. When we pass the numeral to the List.PositionOf, it returns the zero-based offset.

Again, if a value is not present, it returns a -1. So for the first record, we’re passing the value 1. 1 is not present in this list, which is why it returns the value of -1. For the second record, we’re passing the value Third, which is the third item in our list. This returns the value of 2.

## Creating A List Of Dates

Let’s now create a list of dates. We will determine the start date for that month, determine the number of days in that month, and use List.Dates to create a list containing all the days in that month.

Next, we will use List.Select to only retain those dates that are on a specific weekday.

When we click off to the side beside any of these lists, we can see that it only contains these four items.

## Applying Conditional Logic

Finally, we will apply conditional logic to construct or retrieve a date. If the weekday is null, indicating we have a specific date, we can use the intrinsic #date to construct a date value.

If the numeral equals last, then we can use the List.Reverse function to reverse the order in the list and extract the first value.

If we want to change this ascending order into descending, we can always extract the last date from that range of dates. It does not matter if we have four or five elements in the list. By reversing the order and extracting the first item, we always have the last date.

For all the other cases, we use List.Skip.

We can extract the value based on this position on the list. For Thanksgiving day, the numeral is fourth, and fourth returns a 3. This will skip the first three values in the list, and will always return the fourth item.

Let’s put it all together and go to the solution query. For the source step, we’re calling the RAW holidays date table. Next, we’ll add a column that returns a list with dates.

## Reviewing The M Code For The Perpetual Holiday Calendar

Let’s check the M code for the column. For each row in the table, we nested a table containing the distinct years from the dates table. Then we transformed the values in that year column by calling the custom function.

Of course, to be able to invoke the custom function, we have to do some setup first. We have to get the month number, the numeral value, and of course, the weekday. Then from that nested table, we kept the list with dates.

When we click off to the side in the white space, we can see a preview of that list down below.

The next step is to expand on that list and add the CelebratedOnDate.

This is the logic we used in this calculation: if the date is on a Saturday, then we’re going to subtract a day so that we land on a Friday. If it’s a Sunday, then we’re going to add a day to land on a Monday. If it’s not a Saturday or Sunday, then we want a null.

Finally, the last step is to change the types.

Power BI Holiday Calendar â€“ Calculate Date W/DAX
Creating A Calendar Table For Holidays In Power BI
Time Comparison For Non Standard Date Tables In Power BI

## Conclusion

Just a quick reminder that Problem of the Week is an ongoing series that happens every 1st and 3rd Wednesday on the Enterprise DNA forum. The problem posted on the 1st week requires a DAX-based solution, while the 3rd week problem asks for a Power Query-based one.

If you enjoyed the content covered in this POTW, please subscribe to the Enterprise DNA TV channel for more. We have a huge amount of content coming out all the time from myself and a range of content creators, all dedicated to improving the way that you use Power BI and the Power Platform.

Melissa

## Extended Date Table Power Query M Function

In today's blog post, we'll take another look at the Power Query M function for the extended date...

## Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

## Evaluation Context In DAX Calculations

In this tutorial, we'll learn about evaluation context in DAX. Evaluation or initial context is the...

## Icons In Power BI | DAX, UNICHAR, UNICODE & Custom Images

Icons in Power BI are used to communicate meaning and add more context. You can use them as an...

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...