📆Power BI: How to Create an Automatic Calendar Table

⚠ Ver este post na versão em Português ↗

In this tip I will teach you the easiest way to create the time dimension table dCalendar using the CALENDARAUTO() formula.
I will explain how the formula works and show you a practical and quick example.

Understand the CALENDARAUTO() formula:

CALENDARAUTO() reads the data model, recognizes all values in the date fields and automatically creates the dimension table dCalendar with the required date range.

The table will contain only the date range. You can customize the table, complementing it by inserting fields such as Month, Year, Quarter, etc. I will give you a practical example.

Tip summary:

Create a calendar table automatically and complement it.

Tip / Solution:

Before continue, subscribe, it’s free! Follow on social media

In the left sidebar, select “Data”:

Create a new table in “Table tools” → “New table”:

Insert the code below:

dCalendar = CALENDARAUTO()

The table will be created automatically. Wait a few moments, depending on the size of your data model, it will take a few seconds.

Now, we will complement the table. Create a new column under “Table Tools” → “New Column”:

Insert the formula below:

Year = YEAR('dCalendário'[Date])

Result:

To insert more columns (Month, Quarter, etc.), repeat the process “Table tools” → “New Column”:

Insert the codes below for each column:

Month = MONTH('dCalendar'[Date])

Month text = FORMAT('dCalendar'[Date],"MMMM")

Quarter = QUARTER('dCalendar'[Date])

Day = DAY('dCalendar'[Date])

Halfyear = IF('dCalendar'[Month] <= 6,1,2)

Ready! Simple and easy! Feel free to customize your dCalendar table according to your requirements.

Keywords: dCalendario; CALENDARAUTO(); CALENDARAUTO; Calendar; Time; Date; MONTH(); YEAR(); QUARTER(); DAY(); create calendar table power bi; power bi calendar; calendar table power bi dax; power bi calendar automatic; MONTH; YEAR; QUARTER; DAY; power bi dcalendar; how to create power bi calendar table using dax; power bi full calendar table; how to create power bi calendar table

Did you like the content? Want to get more tips? Subscribe for free!

Follow on social media:

• Twitter
• Facebook
• Instagram
• LinkedIn

• 🕖 TIMESTAMP field in SAP BW: How to insert into Transformation routine
  por felipelamounier
  Learn how to add a TIMESTAMP field in SAP BW using an ABAP routine.
• [SAP BW] Step-by-Step: Finding a BW Load Request from an SM37 Job
  por felipelamounier
  Learn how to find a load request from a job in SAP BW using SM37. Guide to identifying load requests in SAP BW jobs through the SM37 transaction. Understanding the process to find load requests in SAP BW using job transaction SM37.
• 💭Old and Current BI Paradigms: a brief reflection
  por felipelamounier
  In this brief reflection, we delve into the transformation of BI concepts, examining its roots and current trends. Follow this journey to understand how changes impact our strategies and decisions