Create a common date table – Model the data

2023-12-03
No Comments

Create a common date table

By default, Power BI creates a calendar hierarchy for each date or date/time column from your data sources.

Need More Review? Auto Date/Time Hierarchies

For detailed considerations and limitations of the auto date/time feature, see “Auto date/time guidance in Power BI Desktop” at https://docs.microsoft.com/en-us/power-bi/guidance/auto-date-time.

While these can be useful in some cases, it’s best practice to create your own date table, which has several benefits:

  • You can use a calendar other than Gregorian.
  • You can have weeks in the calendar.
  • You can filter multiple fact tables by using a single date dimension table.

If you don’t have a date table you can import from a data source, you can create one yourself. It’s possible to create a date table by using Power Query or DAX, and there’s no difference in performance between the two methods.

Creating a calendar table in Power Query

In Power Query, you can use the M language List.Dates function, which returns a list of dates, and then convert the list to a table and add columns to it. The following query provides a sample calendar table that begins on January 1, 2018:

Click here to view code image

let
    Source = #date(2018, 1, 1),
    Dates = List.Dates(Source, Duration.TotalDays(Date.AddYears(Source, 6) – Source),
  #duration(1,0,0,0)),
    #”Converted to Table” = Table.FromList(Dates, Splitter.SplitByNothing(), type
  table [Date = date]),
    #”Inserted Year” = Table.AddColumn(#”Converted to Table”, “Year”, each Date.
  Year([Date]), Int64.Type),
    #”Inserted Month Name” = Table.AddColumn(#”Inserted Year”, “Month Name”, each Date.
  MonthName([Date]), type text),
    #”Inserted Month” = Table.AddColumn(#”Inserted Month Name”, “Month”, each Date.
  Month([Date]), Int64.Type),
    #”Inserted Week of Year” = Table.AddColumn(#”Inserted Month”, “Week of Year”, each
  Date.WeekOfYear([Date]), Int64.Type)
in
    #”Inserted Week of Year”

If you want to add it to your model, you’ll need to start with a blank query:

In Power Query Editor, select New Source on the Home ribbon.

Select Blank Query.

With the new query selected, select Query > Advanced Editor on the Home ribbon.

Replace all existing code with the code above and select Done.

Give your query an appropriate name such as Calendar or Date.

The result should look like Figure 2-7, where the first few rows of the query are shown.

FIGURE 2-7 Sample calendar table built by using Power Query

You may prefer having a table in Power Query when you intend to use it in some other queries, since it’s not possible to reference calculated tables in Power Query.

Creating a calendar table in DAX

If you choose to create a date table in DAX, you can use the CALENDAR or CALENDARAUTO function, both of which return a table with a single Date column. You can then add calculated columns to the table, or you can create a calculated table that already has all the columns.

Note Calculated Tables

We review the skills necessary to create calculated tables in Skill 2.2: Develop a data model.

The CALENDAR function requires you to provide the start and end dates, which you can hardcode for your business requirements or calculate dynamically:

Click here to view code image

Calendar Dynamic =
CALENDAR(
    MIN(Sale[Invoice Date Key]),
    MAX(Sale[Invoice Date Key])
)

The CALENDARAUTO function scans your data model for dates and returns an appropriate date range automatically.

To build a table similar to the Power Query table you built earlier, use the following calculated table formula in DAX:

Click here to view code image

Calendar =
ADDCOLUMNS(
    CALENDARAUTO(),
    “Year”, YEAR([Date]),
    “Month Name”, FORMAT([Date], “MMMM”),
    “Month”, MONTH([Date]),
    “Week of Year”, WEEKNUM([Date])
)

Need More Review? Creating Date Tables

For more examples of how you can create a date table, see “Create date tables in Power BI Desktop” at https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables.