Introduction
For a long time, I’ve relied on DAX to build date tables in tabular models whenever there was no other source available.
I’ve had a template code that I’ve reused countless times, and it works perfectly well in a wide range of scenarios.
I shared this approach with my clients, and they’ve all found it extremely useful.
However, during a recent discussion with a colleague, I discovered a different approach that I hadn’t considered before.
Here, I’ll walk you through various ways to create a date table and compare them.
No matter which method you choose, it’s essential to understand what a date table needs to meet in a semantic model.
What Happens When a Data Warehouse Exists?
Whenever I have access to a centralized data source—such as a relational database, a Fabric Lake, or any other data warehouse—I build the date table there and import it into the semantic model.
These environments offer highly flexible options for building such tables, outperforming both DAX and Power Query in terms of efficiency.
So, there’s no debate on which method to use in this situation.
Date Tables Built with DAX
Creating a date table using DAX is straightforward and relatively simple.
DAX provides a rich set of functions to extend your date table with useful columns and features.
You’ll typically start with the CALENDAR() function, specifying the start and end dates.
You have several options for these dates: you can either set fixed values, derive them from your data using functions like MIN()/MAX(), or use parameters defined in Power Query.
For instance, you might write something like this:
DimDate =
CALENDAR (
DATE ( YEAR (
MIN ( 'Online Sales Order'[Date] )
), 1, 1 ),
DATE ( YEAR (
MAX ( 'Online Sales Order'[Date] )
), 12, 31 )
)
Since Microsoft requires date tables to cover full years, I usually set the start date to January 1st and the end date to December 31st.
Next, you can enrich the table with columns for years, quarters, months, and days.
You can accomplish this within the table definition 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 )
)
One particularly interesting feature is that you can pass a locale identifier to the FORMAT() function—for example, to generate 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" )
)
The result is a table like the one shown below:
Notice the third parameter “de-de” in the FORMAT() calls, which produces columns in both English and German.
With the introduction of user-context-aware calculated columns, this can now be handled in yet another way.
You can read more about this new feature at the link provided.
For columns requiring more complex logic, you can create calculated columns that leverage context transition to reference the whole table.
If you’re unfamiliar with context transition, refer to this article for a detailed explanation:
A good example of this is computing week numbers for fiscal years that don’t align with the calendar year.
My math skills aren’t advanced enough to handle this calculation with a straightforward formula, so I’ll explore alternative methods.
Power Query and Data Flows
Now let’s explore the final approach: leveraging Power Query or Data Flows.
First, I treat Power Query and Data Flows (both v1 and v2) as essentially the same, since they share identical underlying principles and use the same language.
I begin constructing the date table in Power Query by setting up three parameters:
- StartYear: The initial year for the date table
- YearsToLoad: The total number of years the date table should cover
- FirstMonthOfFiscalYear: The starting month of the Fiscal Year.
If the Fiscal Year matches the Calendar year, this value is 1; otherwise, it represents the numerical position of the Fiscal Year’s first month.
All subsequent code depends on these parameters.
The process always begins with the same command: List.Dates()
This function requires three parameters:
- The starting date
- The total number of days to generate
- The interval, which in this case is set to days
This results in a line like the one below, incorporating the parameters mentioned earlier:
List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0))Here we encounter the first challenge:
Typically, we need a date table covering multiple years. However, every fourth year is a leap year.
So how do we handle this, given that Microsoft requires a date table spanning complete years?
The solution involves identifying the last date of the final year (December 31st) and filtering the rows to retain only those on or before this date.
This is precisely why I multiply 366 days by the YearsToLoad parameter.
Below is the complete 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, I can begin adding all the columns necessary to build a comprehensive date table.
First, I include a Date_ID, which provides a numerical representation of the date:
Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date])This column needs to be assigned an integer data type. Therefore, the complete M-Code line is as follows:
Table.AddColumn(#"Keep only valid dates", "Date_ID", each ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)Notice the Int64.Type expression before the closing bracket. This sets the data type within the same command, removing the need for a separate step.
After that, I can use the built-in features in the Power Query Editor to add additional columns that I typically include in my date tables:
As shown, we can add numerous columns without writing any code.
However, at some point, we need to write custom code to add further columns—for instance, columns storing the year and its corresponding period.
Here are some examples of these columns:
- Year/Month Name
- Year/Quarter
- Year/Week
Then there are columns for the start and end dates of any period, such as a week or a month.
I use these columns for custom time intelligence calculations in DAX. I’ve written other articles on this topic, including weekly calculations.
Eventually, standard M-Code becomes insufficient to retrieve the required information.
For example, when I need to obtain a week-aligned year column (YearForWeek).
For these situations, I began writing custom M-functions that enable me to access a date range for each row—something that’s otherwise impossible in M.
In this case, I added the following function:
(DateInput as date) as number =>
let
ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
Year = Date.Year(ClosestThursday)
in
YearIf you’re not acquainted with custom M-functions, I highly recommend exploring this powerful feature.
I’ll include some links in the References section below.
After fully developing the date table, I created these custom functions:
- GetISOYear
Retrieves the week-aligned year - GetISOWeek
Calculates the correct week number based on the ISO Standard - CalculateMonthDiff
Determines the difference in months between two dates - CalculateQuarterDiff
Determines the difference in quarters between two dates - GetFiscalWeekNumber
Calculates the week number starting from the week containing the Fiscal Year’s start date. - GetCurrentFiscalYear
Retrieves the current Fiscal Year based on today’s date. - GetCurrentFiscalStartYear
Calculates the year in which the current Fiscal Year begins.
It took me some time (about 2–3 days of work), but I successfully integrated all the columns into the date table that I find useful in most scenarios.
However, the fundamental concepts in the M-language introduced additional effort and complexity that isn’t required in, for example, SQL.
Rather than pasting all the M-Code here, I’ll provide you with access to the Power BI file containing the complete solution along with
the date table.
What comes next?
Now, you can grab the full M-Code, paste it into a Data Flow, and distribute it throughout your organization.
To enable others to access your Data Flow, simply assign Viewer permissions to users within the Workspace.
This gives you one unified, centralized version of the date table that anyone can use.
This is the key advantage that makes this method extremely valuable.
It works just like having a centralized data platform where you create a date table. But since not everyone has access to such a platform, using a Data Flow serves as a practical alternative.
In my experience working with Data Flows, I’ve noticed that debugging a failed import can be challenging. The error messages are often brief and may lack critical details.
Which approach should you choose?
What would I suggest using?
First, if you already have a centralized data store—whether on-premises or in the cloud, whether it’s a relational database or another type of data store—use that to build your date table.
As I mentioned earlier, there’s no debate about this option.
In a Self-Service BI environment, or when the organization isn’t very large, the choice isn’t as clear-cut.
First, it comes down to the skills available on your team.
After creating the date table in Power Query, I discovered that building a date table in DAX is significantly easier than doing so in Power Query.
DAX provides capabilities that simplify date table creation compared to writing M-Code in Power Query.
I can define the entire table with a single DAX expression and incorporate complex logic through additional calculated columns.
However, each DAX date table is tied to its specific Power BI semantic model. As a result, you end up with multiple date tables that may differ from one another.
But once you have multiple teams developing Power BI solutions, it can be highly beneficial to create one central date table in a single Workspace and share it across all teams.
When someone needs a new feature added to the date table, it gets added to the central version, and everyone benefits from the update.
Naturally, this applies to any form of centralized date table.
In these situations, the data model developer can always choose which columns to import, preventing unnecessary columns from being loaded into the data model.
Conclusion
You now understand the various methods for building a date table.
You can pick from the available options.
However, switching from a local DAX table to a centralized table later on can be difficult.
You should think carefully about which path you’ll take as early as possible to avoid the extra effort of migrating between approaches.
Take your time and discuss with all team members or potential model creators to select the right approach.
There’s no point in deciding to build a centralized date table if nobody actually uses it.
So, make sure everyone agrees to adopt the central date table before moving forward.
References
Here is the Microsoft documentation on custom functions in M:
A Microsoft Learn page covering custom functions:
A helpful explanation from Wicked Smart Data:
If you’d rather watch a video to learn, this one covers custom functions from the basics:
This video explores when custom functions are actually useful:
This one walks you through solving a real challenge, including a very hands-on approach to developing a custom function with ease:
