Create and Use Custom Date Tables in Power BI for Date Hierarchies

By default, Power BI creates a date hierarchy based on the date data in the model. While this default hierarchy works in many cases, it can lead to unexpected results in some visualizations. Custom date tables in Power BI give you more control over date hierarchies, allowing you to customize the behavior, calculations, and formatting of date dimensions.

Instructions

Step 1: Create Date Table Using Power Query

In this example, we use Power Query to generate the date table, though you can also create it using a DAX expression in Power BI.

1. From the Home ribbon, click Get Data and select Blank Query.
2. The Power Query Editor will open a new blank query.
3. In the Power Query Editor, click Advanced Editor on the Home ribbon, then add the following code.
4. Adjust the StartDate and EndDate variables to match the date range needed for your report.
5. Click Done to close the Advanced Editor.
6. After returning to the Power Query Editor, rename the table in the Query Settings pane under PROPERTIES. I’ve named it TBL_PQ_DATE_TABLE.

let
  StartDate = #date(2025, 1, 1),
  EndDate = #date(2025, 12, 31),
  DateList = List.Dates(
    StartDate,
    Number.From(EndDate) - Number.From(StartDate) + 1,
    #duration(1, 0, 0, 0)
  ),
  #"Converted to Table" = Table.FromList(
    DateList,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "DATE"}}),
  #"Add Column YEAR" = Table.AddColumn(#"Renamed Columns", "YEAR", each Date.Year([DATE])),
  #"Add Column QUARTER" = Table.AddColumn(
    #"Add Column YEAR",
    "QUARTER",
    each Date.QuarterOfYear([DATE])
  ),
  #"Add Column MONTH" = Table.AddColumn(#"Add Column QUARTER", "MONTH", each Date.Month([DATE])),
  #"Add Column DAY" = Table.AddColumn(#"Add Column MONTH", "DAY", each Date.Day([DATE])),
  #"Add Column QUARTER_LABEL" = Table.AddColumn(
    #"Add Column DAY",
    "QUARTER_LABEL",
    each "Q" & Number.ToText([QUARTER])
  ),
  #"Add Column MONTH_LABEL" = Table.AddColumn(
    #"Add Column QUARTER_LABEL",
    "MONTH_LABEL",
    each Date.ToText([DATE], "MMM")
  ),
  #"Add Column DAY_LABEL" = Table.AddColumn(
    #"Add Column MONTH_LABEL",
    "DAY_LABEL",
    each Date.ToText([DATE], "ddd")
  ),
  #"Add Column DAY_OF_WEEK" = Table.AddColumn(
    #"Add Column DAY_LABEL",
    "DAY_OF_WEEK",
    each Date.DayOfWeek([DATE])
  ),
  #"Add Column YEAR_QUARTER_LABEL" = Table.AddColumn(
    #"Add Column DAY_OF_WEEK",
    "YEAR_QUARTER_LABEL",
    each Number.ToText([YEAR]) & " " & [QUARTER_LABEL]
  ),
  #"Add Column YEAR_MONTH_LABEL" = Table.AddColumn(
    #"Add Column YEAR_QUARTER_LABEL",
    "YEAR_MONTH_LABEL",
    each Number.ToText([YEAR]) & " " & [MONTH_LABEL]
  ),
  #"Add Column SORT_YEAR_QUARTER" = Table.AddColumn(
    #"Add Column YEAR_MONTH_LABEL",
    "SORT_YEAR_QUARTER",
    each Number.ToText([YEAR]) & Text.PadStart(Number.ToText([QUARTER]), 2, "0")
  ),
  #"Add Column SORT_YEAR_QUARTER_MONTH" = Table.AddColumn(
    #"Add Column SORT_YEAR_QUARTER",
    "SORT_YEAR_QUARTER_MONTH",
    each [SORT_YEAR_QUARTER] & Text.PadStart(Number.ToText([MONTH]), 2, "0")
  ),
  #"Add Column SORT_YEAR_QUARTER_MONTH_DAY" = Table.AddColumn(
    #"Add Column SORT_YEAR_QUARTER_MONTH",
    "SORT_YEAR_QUARTER_MONTH_DAY",
    each [SORT_YEAR_QUARTER_MONTH] & Text.PadStart(Number.ToText([DAY]), 2, "0")
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Add Column SORT_YEAR_QUARTER_MONTH_DAY",
    {
      {"DATE", type date},
      {"YEAR", Int64.Type},
      {"QUARTER", Int64.Type},
      {"MONTH", Int64.Type},
      {"DAY", Int64.Type},
      {"QUARTER_LABEL", type text},
      {"MONTH_LABEL", type text},
      {"DAY_LABEL", type text},
      {"YEAR_QUARTER_LABEL", type text},
      {"YEAR_MONTH_LABEL", type text},
      {"SORT_YEAR_QUARTER", Int64.Type},
      {"SORT_YEAR_QUARTER_MONTH", Int64.Type},
      {"SORT_YEAR_QUARTER_MONTH_DAY", Int64.Type}
    }
  )
in
  #"Changed Type"

7. The resulting table should appear as follows with one entry for each day in the specified date range. Adjust or add columns as needed to the table. As an example, a new column may be needed to calculate fiscal quarters that may not align to standard calendar quarters, e.g., fiscal Q1 may be October, November, and December instead of January, February, and March.
8. From the Home ribbon in the Power Query Editor, click Close & Apply to return to Power BI Desktop.

Step 2: Date Table Adjustments

Returning to the Power BI Desktop, the TBL_PQ_DATE_TABLE is now available in the Data pane.

If any fields in the TBL_PQ_DATE_TABLE are unexpectedly aggregated, be sure to set the summarization attribute to Don't summarize.

1. In the Data pane, click any field in TBL_PQ_DATE_TABLE currently set to summarize.
2. From the Column tools ribbon set Summarization to Don't Summarize.
3. Repeat until all summarizations are removed.

For each of the text based fields, we need to set the Sort by column attribute. This informs Power BI how to correctly sort label columns in calendar order instead of alphabetically, e.g., January, February, March, etc.

1. Click on each label field and set the Sort by column as follows.
2. Repeat until all label fields are configured.

• QUARTER_LABEL sort by column QUARTER
• MONTH_LABEL sort by column MONTH
• DAY_LABEL sort by column DAY_OF_WEEK
• YEAR_QUARTER_LABEL sort by column SORT_YEAR_QUARTER
• YEAR_MONTH_LABEL sort by column SORT_YEAR_QUARTER_MONTH

Next, we’ll create a year / quarter / month hierarchy.

1. In the Data pane, click the YEAR field in TBL_PQ_DATE_TABLE.
2. Click the ellipsis next to YEAR and select Create hierarchy.
3. Power BI will add a YEAR Hierarchy under the TBL_PQ_DATE_TABLE.
4. Click the ellipsis next to the QUARTER_LABEL field and select Add to hierarchy and then YEAR Hierarchy.

Step 3: Mark as Date Table

Now we can inform Power BI to use our custom date table.

1. From the Data pane, click TBL_PQ_DATE_TABLE.
2. From the Table tools ribbon, click Mark as date table.
3. The Mark as a date table dialog box opens.
4. Enable Mark as a date table.
5. Verify that the DATE field is selected in the Choose a date column drop-down list.
6. Click the Save button.

Step 4: Set Data Model Relationships

1. Switch to the Model view.
2. In this example, I have a simple data table called DATA with a date column and a currency amount column.
3. Create a relationship between the DATE columns in TBL_PQ_DATE_TABLE and the DATA table.

Once the relationship is established, the data model appears as follows.

Results

1. Switch to the Report view.
2. To test the new date hierarchy, create a Stacked column chart visualization.
3. Set the X-axis to the YEAR Hierarchy from the date table TBL_PQ_DATE_TABLE.
4. Set the Y-axis to AMOUNT from the DATA table.

The visualization is created with the date hierarchy displayed correctly along the axis after expanding down to the lowest level in the hierarchy.

Summary

Creating a custom date table in Power BI gives you full control over your date hierarchy, ensuring accurate and consistent results in visualizations. By following these steps, you can tailor date dimensions to your specific needs, improving the precision and flexibility of your reports.