AI

How to Build Date Tables in Power BI Self-Service Environments

A practical comparison of DAX, Power Query, and Data Flows for building date tables in Power BI semantic models without a centralized data warehouse.

By Editorial Team ·

How to Build Date Tables in Power BI Self-Service Environments

Introduction

For years, I’ve built date tables in a tabular model with DAX code, when there was no other source for such a table.

I created a template code and reused it over and over again. It works very well in a multitude of situations.

I distributed it to my clients, and they are all happy with it.

But about two weeks ago, I had a discussion with a colleague that opened my eyes to a way to do it, which I didn’t think about until now.

So, let’s look at the variants to build a date table and compare them.

Irrespective of how to do it, it’s important to know the requirements for date tables in semantic models.

What Happens When There Is a DWH?

First, when I have a data store and a source for the semantic model — whether a relational database, a Fabric Lake, or any other centralised data store — I will build it there and consume it in the semantic model.

The options available there for building such a table are very extensive and flexible, and neither DAX nor Power Query is more efficient.

Therefore, there is no question about how to do it in such a case.

DAX Tables

Generating a date table in DAX is relatively easy and straightforward.

DAX offers a great number of functions to add columns and features to a date table.

You always start with the CALENDAR() call to set the start and end date.

You can either use fixed values, like MIN()/MAX() calls based on available data to get the start and end date from a data table inside the data model, or some Power Query parameters.

For example, something like this:

DimDate = 
CALENDAR (
    DATE ( YEAR (
        MIN ( 'Online Sales Order'[Date] )
    ), 1, 1 ),
    DATE ( YEAR (
        MAX ( 'Online Sales Order'[Date] )
    ), 12, 31 )
)

As Microsoft requires having full years in the date table, the range starts with the first of January and ends with the last of December (31.12.).

Next, you can add further columns for years, quarters, months, and days using ADDCOLUMNS():

DimDate = 
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( 'Online Sales Order'[Date] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( 'Online Sales Order'[Date] ) ), 12, 31 )
    ),
    "Date_ID", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonth_ID", CONVERT ( FORMAT ( [Date], "YYYYMM" ), INTEGER ),
    "YearMonth", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "MonthDate", EOMONTH ( [Date], 0 ),
    // User Format String mmm yyyy (Short Month) or mmmm yyyy (Long Month)
    "DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "IsWorkday", IF ( WEEKDAY ( [Date] ) IN { 1, 7 }, 0, 1 ),
    "SemesterNumber", IF ( INT ( FORMAT ( [Date], "MM" ) ) <= 6, 1, 2 ),
    "Semester", IF ( INT ( FORMAT ( [Date], "MM" ) ) <= 6, "S1", "S2" ),
    "YearSemesterNumber",
        IF (
            INT ( FORMAT ( [Date], "MM" ) ) <= 6,
            YEAR ( [Date] ) * 10 + 1,
            YEAR ( [Date] ) * 10 + 2
        ),
    "YearSemester",
        IF (
            INT ( FORMAT ( [Date], "MM" ) ) <= 6,
            FORMAT ( [Date], "YYYY" ) & "/S1",
            FORMAT ( [Date], "YYYY" ) & "/S2"
        ),
    "QuarterNumber", INT ( FORMAT ( [Date], "q" ) ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarterNumber",
        YEAR ( [Date] ) * 10 + FORMAT ( [Date], "Q" ),
    "YearQuarter",
        FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "DayOfMonth", FORMAT ( [Date], "DD" ),
    "DayOfYear", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
    "DayOfYear_woWeekend", NETWORKDAYS ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], 1 ),
    "RestDaysInYear",
        DATEDIFF (
            DATE ( YEAR ( [Date] ), 1, 1 ),
            DATE ( YEAR ( [Date] ), 12, 31 ),
            DAY
        )
            - DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
    "RestDaysInYear_woWeekend",
        NETWORKDAYS (
            DATE ( YEAR ( [Date] ), 1, 1 ),
            DATE ( YEAR ( [Date] ), 12, 31 ),
            1
        )
            - NETWORKDAYS ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], 1 ),
    "WeekNumber", WEEKNUM ( [Date], 21 )
)

It is also possible to pass a locale setting to the FORMAT() function to create month names in different languages:

DimDate = 
ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MIN('Online Sales Order'[Date])), 1, 1)
            ,DATE(YEAR(MAX('Online Sales Order'[Date])), 12, 31)
            ),
"Date_ID", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonth_ID", CONVERT(FORMAT ( [Date], "YYYYMM" ), INTEGER),
"YearMonth", FORMAT ( [Date], "YYYY/MM" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameShort_DE", FORMAT ( [Date], "mmm", "de-de" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"MonthNameLong_DE", FORMAT ( [Date], "mmmm", "de-de" ),
"DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeek_DE", FORMAT ( [Date], "dddd", "de-de" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"DayOfWeekShort_DE", FORMAT ( [Date], "ddd", "de-de" )
)

This results in a table like this:

Date table created with DAX with columns in multiple languages Figure 1 – Date table created with DAX with columns in multiple languages

Note the third parameter "de-de" of the FORMAT() call and the corresponding columns in the table — one in English and one in German.

With the advent of user-context-aware calculated columns, this can also be implemented differently. Read here for more information about this new feature.

In cases where columns require more complex logic, you can use calculated columns with context transition to access the entire table.

One example of this is calculating the week number for fiscal years when they don’t align with calendar years. Doing this with a mathematical formula is non-trivial, and it is generally easier to handle through calculated columns with context transition.

Power Query and Data Flows

Now we come to the last variant: using Power Query or Data Flows.

Power Query and Data Flows — whether v1 or v2 — are not distinguished here, as they all operate on the same principles and use the same language.

Building the date table in Power Query starts with creating three parameters:

  1. StartYear: The first year in the date table
  2. YearsToLoad: How many years should be covered by the date table
  3. FirstMonthOfFiscalYear: Which is the first month of the fiscal year. If the fiscal year aligns with the calendar year, this will be 1; otherwise, it will be the number of the first month of the fiscal year.

All further code relies on these parameters.

The starting point is always the same function: List.Dates()

Its parameters are:

  1. The starting date
  2. The number of days to create the list
  3. The interval, which in this context is days

This produces a line like the following, using the parameters mentioned above:

List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0))

This immediately raises a consideration: a date table typically spans multiple years, and every fourth year is a leap year. Since Microsoft requires the date table to span entire years, the solution is to derive the last date of the final year (31 December) and filter rows to keep only those on or before that date. This is why YearsToLoad is multiplied by 366 days.

Here is the full M-Code for this scenario:

let
    Source = List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Last Valid Date" = Table.AddColumn(#"Changed Type", "Last Valid Date", each #date(Date.Year(List.Max(#"Changed Type"[Date])) - 1, 12, 31), type date),
    #"Keep only valid dates" = Table.SelectRows(#"Added Last Valid Date", each [Date] <= [Last Valid Date])
in
    #"Keep only valid dates"

Next, additional columns can be added to build a complete date table.

First, a Date_ID column with a numerical representation of the date:

Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date])

This column must be set to an integer data type, so the full line of M-Code is:

Table.AddColumn(#"Keep only valid dates", "Date_ID", each ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)

Note the Int64.Type expression before the last closing bracket — this sets the data type within the same step, eliminating the need for an additional transformation.

From here, the built-in Power Query Editor features make it easy to add many more columns without writing code by selecting the Date column and navigating to the Add Column ribbon:

Built-in feature to add columns based on a date column in Power Query Figure 2 – Built-in feature to add columns based on a date column, available from the Add Column ribbon after selecting a date column

Beyond what the UI provides, custom M-Code is needed for columns such as:

  • Year/Month Name
  • Year/Quarter
  • Year/Week
  • Start and end dates for periods like weeks or months

These columns are particularly useful for custom time intelligence calculations in DAX, such as weekly calculations.

For more advanced scenarios — such as deriving a week-aligned year column (YearForWeek) where fiscal years don’t align with calendar years — standard M-Code is insufficient. Custom M-functions provide a way to access a date range for each row, enabling logic that would otherwise be impossible to express in a single M expression.

Each approach — DAX calculated tables, Power Query, and Data Flows — has its place depending on the complexity of the date logic required, the need for multi-language support, and whether fiscal year alignment must be handled. Choosing the right method from the outset saves significant rework as requirements grow.