Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server and Azure SQL - How to Create a Calendar Table (Date Dimension) Using SQL (Including Holidays)

Post Views 1,230 views
Reading time 15 minutes

Hey guys!
All right with you?

In this post I would like to bring my opinion on the best way to create a calendar table. This table, often used for BI/Analytics scenarios, serves to aggregate data by different time/date dimensions and is present in almost 100% of reports and BI projects, making it a good practice to use it.

It is worth remembering that it is a bad practice to use the default Power BI date hierarchy (See more about this by clicking in this link here), and should only be used in very small, occasional projects or by novice users.

Any scenario other than this should require the use of Calendar tables instead of using Power BI's default hierarchy, which creates a very basic internal calendar table for each date-type column that exists in the model, thus impairing performance. of model.

Many argue that it is better to create using SQL, others in M, others in DAX and the discussion goes on longer than it should.

How best to create a calendar table

There are several very interesting articles about this discussion and I would highlight the article Power BI Date or Calendar Table Best Method: DAX or Power Query? as one of the best I've read on the subject.

And I'll give my opinion on this:

DAXM / PowerQuerySQL
I can create a calendar tableYesYesYes
It's easy to create the calendar tableYesYesYes
My calendar table respects the range of my dataYesYes, using parametersYes, filtering in the query
I can calculate national, mobile and state holidaysNoYes, but it takes a lot of workYes
Can I reuse it in other reports (without duplicating code)NoYes, but only using DataflowYes
Can I reuse using other Dataviz toolsNoNo (unless using PowerQuery online or ADF/Synapse/Fabric)Yes

Now that I have expressed my opinion, in which I believe that the calendar table would be better used if created in a DW base using SQL, for better reuse of data in any scenario, project, report or tool, I will show you how to create your table calendar using SQL.

How to Create a Calendar Table Using SQL (Including Holidays)

Back on July 29, 2015, just 5 days after the official launch of Power BI (and which would only be available in Brazil several months later), I had already posted the article How to calculate working days in SQL Server (Table dCalendar), where I show how to create a calendar table using SQL.

In this post, I would like to update this solution and present you with some calendar table models, so that you can use the model that best suits your needs.

Model 1 - Simplified table

If you need something simple, fast and objective, without worrying about holidays and working days, this is a good option for you.
Simplified calendar table without holidays and working days

Table result:

Model 2 - Simplified Table IN ENGLISH

If you need something simple, fast and objective, in the ENGLISH language, without worrying about holidays and working days, this is a good option for you.
The last parameter of the function is the number of months of the FiscalYear, which add N months in the fiscal columns.
Table-valued function that generates a simplified ENGLISH calendar table without holidays and weekdays

Result:

Model 3 - Complete table, with holidays and working days

If you need a calendar table that handles working days and holidays, this is the table for you, even if it takes 1 or 2 minutes to generate.

Complete calendar table, without national, state and mobile holidays

Result:

To learn more about the holiday table, visit the article How to create a table with holidays (national, state and mobile) in SQL Server.

And that's it, folks!
And you? How do you prefer to create your calendar table? Using SQL, M or DAX?

I hope you enjoyed this article and see you next time 🙂