Add a date dimension

  • 6 minutes

Power BI Desktop works behind the scenes to automatically identify columns that represent dates, and then creates date hierarchies and other enabling metadata for your model, on your behalf. You can then use those built-in hierarchies when creating report features like visuals, tables, quick measures, slicers, and so on. Power BI Desktop does this by creating hidden tables on your behalf, which you can then use for your reports and DAX expressions.

Many data analysts prefer to create their own date tables, which is fine. In Power BI Desktop, you can specify the table you want your model to use as its date table, and then create date-related visuals, tables, quick measures, and so on, using that table's date data. When you specify your own date table, you control the date hierarchies created in your model, and use them in quick measures and other operations that use your model's date table.

To add a Date table to your Power BI report, follow these steps:

  1. In the Data tab of Power BI Desktop, select Table Tools and then New Table.

    Screenshot of the Add a date table feature.

  2. Expand the dropdown and put in the following DAX expression:

    Screenshot of the Add date table using a DAX expression feature.

  3. Hit enter and let the report execute the query. In case there are no results, click the Refresh button in the Home tab.

  4. A Date table is now added to your data model.

    Screenshot of the Add date table result.

  5. Next open the Model tab and create a relationship between the Date field from the Date table and the ShipmentDate field from the OrderIntake table.

    Screenshot of the Add date table relationship.

  6. To set the date table, select the table you want to use as a date table in the Fields pane, then right-click the table and select Mark as date table > Mark as date table in the menu that appears:

    Screenshot of the Mark as date table feature.

  7. As an alternative, you can also select the table and then select Mark as Date Table from the Modeling ribbon.

  8. When you specify your own date table, Power BI Desktop validates the following on that column and its data, to ensure that the data:

    • contains unique values

    • contains no null values

    • contains contiguous date values (from beginning to end)

    • if it is a Date/Time data type, it has the same timestamp across each value

It's important to note that when you specify your own date table, Power BI Desktop does not auto-create the hierarchies that it would otherwise build into your model on your behalf. If you later deselect your date table (and no longer have a manually set date table), Power BI Desktop recreates the automatically created built-in date tables for you, for the date columns in the table.

Also it's important to note that when you mark a table as a date table, it removes the built-in (automatically created) date table that Power BI Desktop created. Any visuals or DAX expressions you previously created based on those built-in tables will no longer work properly.

In the example above, we used the following DAX expression to generate the Date table:

Date =
    ADDCOLUMNS (
        CALENDARAUTO(),
        "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
        "Year", YEAR ( [Date] ),
        "Monthnumber", FORMAT ( [Date], "MM" ),
        "MonthName", FORMAT ( [Date], "mmmm" ),
        "DayOfWeekNumber", WEEKDAY ( [Date] ),
        "DayOfWeek", FORMAT ( [Date], "dddd" ),
        "Quarter", "Q" & FORMAT ( [Date], "Q" )
    )

You can use other DAX expressions and even modify the above expression to add or remove columns you might require. To do this, you can use the DAX language and that's exactly what we'll discuss in the next unit.