By default, Power BI creates a date hierarchy based on the date data it identifies in the data model. This default hierarchy may be sufficient in many instances. However, you may quickly find it produces unexpected results in some visualizations. Custom date tables in Power BI provide fine-tuned control over date hierarchies. By creating a custom date table, you have complete control over the date hierarchy behavior as well as the calculation and formatting of various date dimensions.
Step 1 – Create Date Table Using Power Query
In this example, Power Query is used to generate the date table. I chose Power Query out of personal preference, but the date table may also be created using a DAX expression in Power BI.
On the Home ribbon, click Get data and select Blank query. The Power Query Editor opens to a blank query. On the Home ribbon in the Power Query Editor, click Advanced Editor and add the following code. Adjust the StartDate and EndDate values to satisfy the minimum and maximum date range needed in your specific report. Click Done to close the Advanced Editor. After returning to the Power Query Editor, rename the table in the Query Settings pane under the PROPERTIES section. I use the name TBL_PQ_DATE_TABLE.
let
StartDate = #date(2022, 1, 1),
EndDate = #date(2022, 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"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.
On 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 Fields pane.
If any fields in the TBL_PQ_DATE_TABLE are unexpectedly aggregated, be sure to set the summarization attribute to Don’t summarize. In the Fields pane, click any field in TBL_PQ_DATE_TABLE currently set to summarize. On the Column tools ribbon set Summarization to Don’t Summarize. 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. Click on each label field and set the Sort by column as follows:
- 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. In the Fields pane, click the YEAR field in TBL_PQ_DATE_TABLE. Click the ellipsis next to YEAR and select Create hierarchy. Power BI will add a YEAR Hierarchy under the TBL_PQ_DATE_TABLE. 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. In the Fields pane, click TBL_PQ_DATE_TABLE. In the Table tools ribbon, click Mark as date table followed by Mark as date table. The Mark as date table dialog box opens. Verify that the DATE field is selected in the Date column drop-down list and then click the OK button.
Step 4 – Set Data Model Relationships
Switch to the Model view. In this example, I have a simple data table called DATA with a date column and a currency amount column. 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.
Result
Switch to the Report view. To test the new date hierarchy, create a Stacked column chart visualization with the YEAR Hierarchy from the date table TBL_PQ_DATE_TABLE along the Axis and AMOUNT from the DATA table in Values.
The visualization is created with the date hierarchy displayed correctly along the axis after expanding down to the lowest level in the hierarchy.
