📆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

• How to Find Deleted VTTK Transport in SAP ECC
  por felipelamounier
  Bem Vindo! | Welcome! By Felipe Lamounier, Minas Gerais, Brasil🇧🇷 – powered by 🙂My Easy B.I. 📑 Table of Contents: Introduction In this post, we will learn how to identify which transports were deleted in SAP ECC. We will also see how this data is removed from the VTTK table over a specific period. InContinuar lendo “How to Find Deleted VTTK Transport in SAP ECC”
• How to identify SAP BW Process Chains with Recurring Errors
  por felipelamounier
  This post by Felipe Lamounier focuses on identifying recurring errors in Process Chains (RSPC) within SAP BW over a 65-day period. It provides a structured approach to filter and analyze execution logs, aimed at improving resource management and system efficiency by addressing improperly executed chains. The analysis concludes with recommendations for maintaining or removing chains based on error frequency.
• How to Retrieve SAP Table Metadata Efficiently
  por felipelamounier
  This post by Felipe Lamounier provides a guide on efficiently retrieving metadata from SAP table fields using transaction SE16 and the DD03M view. Key elements include data element, data type, field length, and descriptions. Additionally, the post lists important SAP system tables, enhancing understanding of SAP metadata extraction.