# 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.

## 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

## Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

## Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...