How to Create a Date Table in Microsoft Power BI
Your email has been sent
Once you decide the default Auto Date table isn’t adequate, you can create one that fulfills your grouping and filtering requirements in Microsoft Power BI.
The article How to know if the Auto Date table is adequate when using Power BI provides a look into the inner workings of how Microsoft Power BI handles dates and times. It’s the quickest and easiest option because you do nothing at all. Although Power BI will create an internal date table for you, it won’t always produce the filtering and grouping requirements you need. When this happens, you can create the date table yourself. Having the skill to create your own is an advantage when you’re working with a complex dataset.
In this tutorial, I’ll show you how to create a date table when the internal default table isn’t adequate using Data Analysis Expressions. If you’re not familiar with date tables, I recommend that you read the linked article above before you tackle creating a date table yourself.
I’m using Microsoft Power BI Desktop on a Windows 11 64-bit system. Throughout the article, I will use the terms date table and facts table to describe the date table and data tables, respectively, because the terms date and data are too similar and might be confused.
You can download the Microsoft Power BI demo file for this tutorial.
A date table is a table of dates and other metadata about those dates. The relationship between facts tables and the date table allows users to filter and compare data by time periods, such as months and years.
It’s best to know up front if you plan to create a date table because Power BI allows only one date table. If you build visuals on the internal Auto Date table and then create your own date table and mark it as such, Power BI will destroy the Auto Date table and all visuals based on it.
SEE: Explore this side-by-side analysis of Google Workspace vs. Microsoft 365.
A date table looks like any other table with a row for every date. The first column is a date and time data type column named Date. The remaining columns store metadata about each date such as the year, quarter, month and so on for the date (Figure A).
Figure A
When you use a custom date table, you control the date hierarchies used by your model. This trickles down to quick measures and other evaluations. It’s difficult for a facts table to meet the requirements for being a date table:
If you do choose to use a facts table, you can define it as the date table as follows:
When you mark a facts table as a date table, Power BI will build the relationships and hierarchies based on this table. Otherwise, you’ll have to create the necessary relationships between the date (facts) table and the other tables to get the same results.
SEE: Here’s how to create a dashboard in Power BI.
When you need to create a custom date table, you can use DAX to create a calculated table. DAX is an expression language used in Analysis Services, Power BI and Power Pivot that includes functions and operators.
You can use either the DAX CALENDAR or CALENDARAUTO function to create a date table. Both return a single-column table of dates.
When considering which function to use, CALENDAR requires the first and last dates so it can generate a full list of dates. CALENDARAUTO uses existing dates in a facts table to generate a list of dates. We’ll use CALENDAR to create a date table.
The CALENDAR function uses the following syntax:
CALENDAR(<start_date>, <end_date>)
Where start_date is the first date in the resulting date table and end_date is the last. The function will return a one-column table populated with a list of dates from start_date to end_date and every day in-between.
Now, let’s use CALENDAR to create a date table in Power BI using January 1, 2000, as start_date and December 31, 2021, as end_date:
Figure B
The function creates the new table, names the single column Date and populates that column appropriately. It’s worth noting that there are 1,096 distinct values, or rows. However, 365 * 3 is 1,095. Power BI knows that 2020 was a leap year.
The next step is to add columns for each date component that you’ll need for sorting and filtering: week number, month number, quarter, year and so on. At this point, you can use the Add Column option to add more columns. However, you don’t have to. It’s more efficient to add all of those columns when you create the table:
Date =
ADDCOLUMNS (
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
“Year”, YEAR([Date]),
“MonthNumber”, MONTH([Date]),
“Quarter”, QUARTER([Date]),
“DayOfWeek”, WEEKDAY([Date])
)
Figure C shows the results. The number of columns you add depends on the filtering and grouping needs of your visualizations and reports.
Figure C
SEE: Learn how to use Power BI in Outlook and Office for data storytelling.
For this example, the function creates only a few columns, but there are many more you might need. For a complete list, visit Date and time functions (DAX) – DAX | Microsoft Learn.
This expression combines ADDCOLUMNS and CALENDAR:
At this point, things get a little muddy. You must decide whether to mark the custom table as a date table, as discussed earlier in reference to marking a facts table as a date table. Doing so will create the custom hierarchies defined by the date table.
SEE: Follow along in this guide to add a calculated column in Microsoft Power BI.
On the other hand, if you’d like Power BI to do this for you, don’t mark the table as a date table. You can create the relationships and use it for specialized grouping and filtering instead. This is one of those areas where there’s no right or wrong, but knowing your data is key to making the most efficient choice.
Remember: When you mark a facts table as a date table, Power BI removes the built-in Auto Date table and any visuals you previously built on it. If you deselect the manual date table, Power BI will automatically create a new Auto Date table.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to Create a Date Table in Microsoft Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply