Power BI Holiday Calendar – Calculate Date W/DAX

Creating a Power BI holiday calendar is crucial for many time intelligence calculations. In this tutorial, I’m going to show you how to calculate specific holiday dates using DAX measures. You can watch the full video of this tutorial at the bottom of this blog.

This example came from one of the Enterprise DNA initiatives called Problem of the Week, where each month we take a real-world DAX or Power Query problem and break it down in detail.

I used a technique called Rubber Duck Debugging. It’s typically used for debugging, but I find it’s also really valuable for coming up with the initial framework that you’re going to use to solve a complex problem like in Power BI.

It’s basically just using a rubber duck or a person that you talk to and say the solution that you’re envisioning out loud and developing a plan for how you’re going to attack the problem.

The mere act of voicing that often helps you work through the problem. In some cases in a debugging situation, it makes you realize what you’re doing, whether it makes sense, and helps find a way to fix it by addressing the problem.

Below is the real-world DAX problem for this particular challenge. We have a table here with 10 holidays with the month and day. Some of the days are specific days and some of them are relative ones. We need to come up with the Holiday Date and the Celebrated On measures.

To do that, we need to parse the Day field into two components. The first parse is the occurrence (third, last, first, second) and the second parse is the day (Monday, Thursday). These are two critical pieces of information that we need to separate.

Table of Contents

Calculating Specific Dates In Power BI Holiday Calendar

Normally, I would do this in Power Query just through a split by delimiters, but since we’re doing it with DAX, we’re going to have to do it a different way. (Note that we’re not allowed to use the Power Query in this challenge.)

We need to calculate the minimum and maximum dates for the specified year, month, and day name. In this case, for example, we can say for 2027, January, and the third Monday. Once we get that minimum day for a given month, we can add seven days to it to get to the second Monday, 14 days to get to the third Monday, and 21 days to get to the fourth Monday.

Another technique that I want to share with you related to this challenge is what I call Building in Parts when creating a measure. I find this really helpful in a complex measure where if you have a lot of variables, you have a lot of components to build.

What I always do is I have a result variable (VAR Result) at the end and my last statement that is just Return Result. This makes it really easy to go back and test the variables I’ve built. I can go through and track and solve it in parts.

The last technique that I want to share with you is what I call Searching for Unknown Functions. For instance, if I don’t know what function to use for this Power BI holiday calendar challenge, I’ll go to the External Tools tab and go to the SQLBI DAX Guide.

It has a really nice feature here called Groups (on the left side), where the functions are grouped by type. And so, if I’m thinking about parsing a text string, for example, I can go search for Text functions.

It provides all the text functions that DAX has. This is a great way to quickly find the DAX function you need for a measure.

DAX Measures For The Power BI Holiday Calendar

For the Holiday Date measure, I created this series of SELECTEDVALUEs, harvesting the dates from the slicer, the holiday month, the month number, and the day from the Date table.

Then, I have the length from that selected day using the length (LEN) function. In this case, it returned text, so I had to wrap it in VALUE to change that text to a numeric value.

And then, I used the SEARCH function for the position of the space and wrapping it VALUE, but saying, search for space within the selected day, starting at position one and returning zero if I don’t find that string in there.

The CharsBeforeSpace (characters before space) is just the SpacePosition. The number of CharsAfterSpace (characters after space) is just the length (SelDayLen) of the string minus the position of the space. From that, we can do the FirstParse and the SecondParse.

At this point, we now have two variables, one representing the occurrence and one representing the day.

And then, we have a basic CALCULATE MIN (VAR Calc1st) where we take the filter off the Dates table. Then, we apply the filters of the selected year from the slicer, the month name, and then the day of the week from that second parse that we did.

Similarly, we do the same thing for the last calculation (VAR CalcLast).

Once we have all that, it goes to a SWITCH TRUE statement and the last part is the RETURN Result.

Lastly, I want to walk you through the Celebrated On measure as an add-on bonus from the challenge. This is just a simple LOOKUPVALUE where I took the holiday date that we calculated in the previous measure, went to the Dates table, and looked up the day of the week name. And then I just did a SWITCH statement where we took that result of the DayLookup. And finally, I wrap it up with a RETURN DateAdjust.

Conclusion

This Power BI Holiday Calendar challenge, as I mentioned, is a real-world problem. The solution that I came up with is quite complex, but really effective in calculating the specific holiday dates.

I hope you learned a lot from this tutorial. Check out the full video tutorial for a more detailed walk-through explanation of the DAX measures.

Cheers!

Brian