Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Date Table


Share

Why a Date Table?

A date table is a dimension table with one row for every day, and columns such as Year, Quarter and Month.

In the First Report chapter, Power BI made a Year, Quarter, Month and Day hierarchy for the Date column by itself.

This feature is called auto date/time.

It is handy for a quick report, but it has limits:

  • Power BI adds a hidden date table for every date column, which makes the model bigger.
  • You cannot see those hidden tables, or add your own columns to them, such as a fiscal year.
  • Each hidden table belongs to one column. One date table cannot filter several fact tables, for example sales and targets.

Your own date table fixes all three.

It is also needed for the time intelligence functions in the DAX chapter, such as sales year to date, or sales in the same period last year.

A date table must follow a few rules:

  • One row per date, with no date twice and no blank dates.
  • No missing days between the first date and the last date.
  • Whole years, from January 1 to December 31.

Why not just use the Date column in Sales?

In the Poke Mart data, every day of 2024 and 2025 happens to have at least one order.

A real shop has days without sales, such as holidays, and those dates would be missing.


Create the Date Table with DAX

You create the date table with one line of DAX.

Open poke_mart_model.pbix, the file you built in the Star Schema chapter and changed in the chapters after it, then:

  1. Select Report view.
  2. On the Modeling tab, select New table. A formula bar opens above the canvas.
  3. Replace the text in the formula bar with the formula below, and press Enter.

Example

Date = CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31))

Report view with the Modeling tab selected, the New table button outlined, and the formula bar showing Date = CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31))

A new table called Date appears in the Data pane.

Power BI also adds the Table tools tab to the ribbon and switches to it.

In the picture, the Modeling tab is selected again to show the New table button.

This is how the formula works:

  • Date = names the new table.
  • DATE(2024, 1, 1) builds a date from a year, a month and a day: January 1, 2024.
  • CALENDAR returns a table with one column, also called Date, with every day from the first date to the last date, both included.

The table has 731 rows: 366 days in 2024, which is a leap year, and 365 days in 2025.

A table made by a DAX formula is called a calculated table.

It lives in the model, next to the tables you loaded from Excel.

Select the new Date column in the Data pane.

On the Column tools tab, Data type shows Date/time.

Change it to Date, because the table holds days, not times.

The dates then show in the long date format, such as Monday, January 1, 2024.


Add the Date Columns

Now add four columns to the Date table. For each one:

  1. Select the Date table in the Data pane.
  2. On the Table tools tab, select New column.
  3. Type the formula in the formula bar and press Enter.

Year

Year = YEAR('Date'[Date])

Quarter

Quarter = "Q" & QUARTER('Date'[Date])

Month Number

Month Number = MONTH('Date'[Date])

Month

Month = FORMAT('Date'[Date], "MMM")
  • YEAR returns the year, such as 2024.
  • QUARTER returns a number from 1 to 4. The & sign joins it to the letter Q, so you get Q1 to Q4.
  • MONTH returns a number from 1 to 12.
  • FORMAT with "MMM" returns the short month name: Jan, Feb, Mar and so on.

'Date'[Date] means the Date column of the Date table.

The table name is in single quotes, because Date is also the name of a DAX function.

A column made by a DAX formula is called a calculated column.

You learn more about them in the DAX Calculated Columns chapter.

Select Table view and select the Date table.

The first rows look like this:

DATEDate123YearABCQuarter123Month NumberABCMonth
Monday, January 1, 20242024Q11Jan
Tuesday, January 2, 20242024Q11Jan
Wednesday, January 3, 20242024Q11Jan
Thursday, January 4, 20242024Q11Jan
Friday, January 5, 20242024Q11Jan
Saturday, January 6, 20242024Q11Jan
Sunday, January 7, 20242024Q11Jan
...............
Wednesday, December 31, 20252025Q412Dec

731 rows in total, from Monday, January 1, 2024 to Wednesday, December 31, 2025.

Year and Month Number are numbers, so Power BI would add them up in a visual.

Select each of them, and on the Column tools tab set Summarization to Don't summarize, as you did for OrderID.



Sort Month by Month Number

Month is a text column, so Power BI sorts it alphabetically:

Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep

That is not the order anyone wants.

Tell Power BI to sort Month by Month Number instead:

  1. Select Report view.
  2. In the Data pane, expand the Date table and select Month.
  3. On the Column tools tab, select Sort by column > Month Number.

Every visual that uses Month now shows the months in calendar order:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

This works because each month name has exactly one month number.


Mark as Date Table

Next, tell Power BI that this table is the calendar of the model:

  1. In the Data pane, select the Date table.
  2. On the Table tools tab, select Mark as date table.
  3. In the dialog, turn on Mark as a date table, and in the Choose a date column list select Date.
  4. Power BI checks the column: unique values, no blanks, no missing days. When the check passes, the dialog shows Validated successfully. Select Save.

The Mark as a date table dialog with the option turned on, the Date column chosen as the date column, and the message that the column was validated

You can also right-click the Date table in the Data pane and select Mark as date table.

Marking the table does two things.

The time intelligence functions in DAX know which table and column to use.

And Power BI stops making its own hidden date table for this table.


Relate the Date Table to Sales

The Date table is not connected to anything yet.

Connect it to the Date column in Sales:

  1. Select Model view.
  2. Drag Date from the Sales table onto Date in the Date table.
  3. The New relationship dialog opens, with Date already selected in both tables. Select Save.

Check that the Date column in Sales has the data type Date too.

Both sides of the relationship should hold plain dates.

Double-click the new line to check its settings: Many to one (*:1) from Sales to Date, with Single cross-filter direction.

Each day appears once in Date, and on many orders in Sales.

The model now has three relationships, all pointing out from Sales:

From (many side)To (one side)
Sales[ProductID]Products[ProductID]
Sales[StoreID]Stores[StoreID]
Sales[Date]'Date'[Date]

Test the relationship in Report view.

Build a table visual with Year from the Date table and Quantity from Sales:

YearSum of Quantity
202411,277
202513,211
Total24,488

Each year shows its own number, so the filter travels from Date to Sales.

If both years showed 24,488, the relationship would be missing.


Turn Off Auto Date/Time

Now that the model has a real date table, turn off auto date/time for this file:

  1. Select File > Options and settings > Options.
  2. Under CURRENT FILE, select Data Load.
  3. In the Time intelligence section, clear the Auto date/time check box.
  4. Select OK.

The Options dialog in Power BI Desktop with Data Load selected under CURRENT FILE, and the Auto date/time check box cleared in the Time intelligence section

Power BI removes all the hidden date tables from the file.

From now on, all filtering and grouping by dates goes through your Date table.

Note: Turning off auto date/time can break visuals that use the automatic hierarchy, such as a line chart built on Sales[Date].

Rebuild them with Year, Quarter and Month from the Date table.

To turn the option off for all new files, clear Auto date/time for new files on the Data Load page under GLOBAL in the same dialog.

Save your file.

In the next chapter you add hierarchies, including Year > Quarter > Month in the Date table.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.