How do I add week numbers to a date table in Power BI?

After creating the date table, add a calculated column with the DAX formula: WeekNumber = WEEKNUM(DateTable[Date], 2). The second parameter determines which day the week starts on: use 1 for Sunday or 2 for Monday. The column returns the week number of the year, so you can easily filter or group by week number in your visuals.

How do I add weekday names to a date table in Power BI?

Add a calculated column with the DAX formula: Weekday = FORMAT(DateTable[Date], 'dddd'). This returns the full name of the day, such as Monday, Tuesday, and so on. If you want to sort the days in the correct order in a chart, also add a column with the day number: WeekdayNumber = WEEKDAY(DateTable[Date], 2). Then sort the Weekday column by the WeekdayNumber column via 'Column tools > Sort by column'.

How do I make the date table dynamic so it updates automatically?

Replace the fixed start and end date in the CALENDAR formula with dynamic variables. For example, use MINX(FactTable, FactTable[Date]) as the start date and TODAY() as the end date. This way the date table automatically grows with your data. Also remember to mark the table as a date table via 'Modeling > Mark as Date Table' and select the Date column for optimal functioning of time intelligence functions in DAX.

Power BI: Date table with week numbers and weekdays

A good date table is essential for time-based analyses in Power BI. In this article you will learn how to create a dynamic date table that automatically includes week numbers and weekdays. This helps you build flexible and insightful reports that are easy to filter by time unit.

What you need

A Power BI report where you want to add a date table
Basic knowledge of DAX formulas
A start and end date for the period you want to analyse

Step 1: Create a base date table with DAX

We start by creating a date table that automatically generates all dates between a start and end date. This is the foundation for further extensions.

In Power BI Desktop, add a new table via Modeling > New Table and use the following DAX formula:

DateTable =
VAR StartDate = DATE(2023,1,1)
VAR EndDate = DATE(2024,12,31)
RETURN
CALENDAR(StartDate, EndDate)

This table now contains all dates in the specified period.

Step 2: Add week numbers and weekdays

To make the date table more useful, we add columns for week number and weekday. This makes it possible to easily filter and group by these time units.

Add the following calculated columns to the date table:

WeekNumber = WEEKNUM(DateTable[Date], 2)  // 2 means the week starts on Monday

Weekday = FORMAT(DateTable[Date], "dddd")

The WeekNumber column gives the number of the week in the year, and Weekday shows the name of the day, such as Monday, Tuesday, etc.

Step 3: Make the date table dynamic and connect it to your model

To make the date table dynamic, you can replace the start and end date with variables that are based on your data or parameters, for example. This ensures the table automatically grows with your dataset.

It is also important to mark the date table as a date table in Power BI via Modeling > Mark as Date Table and select the Date column. This improves the time intelligence functions.

Then connect the date table to your fact tables via the date columns to enable time-based analyses.

Tips & common mistakes

Always use a date table: This prevents issues with time filters and makes analyses more reliable.
Week number setting: Use the second parameter in WEEKNUM to determine which day the week starts on (1 = Sunday, 2 = Monday).
Mark the date table: Don’t forget to mark the table as a date table for optimal functionality.
Avoid hardcoded dates: Make the start and end date dynamic where possible to reduce maintenance.

Example: Use in a sales dashboard

In a sales dashboard you can use this date table to easily analyse sales by week number or by weekday. For example, you can see which weekdays perform best or how sales develop per week in the year. This helps with making targeted actions and reports.

Conclusion

With this dynamic date table with week numbers and weekdays you significantly improve time analysis in your Power BI reports. Try it out directly in Power BI Desktop or the online environment. For more tips on date tables, you can also read the article about creating a date table with fiscal weeks.

Our tip: Always sort the weekday column by a separate weekday number column so that the days appear in the correct order in visuals. Without explicit sorting, Power BI sorts weekday names alphabetically, which would put Tuesday before Monday. Add a column with WEEKDAY(DateTable[Date], 2) and use ‘Sort by column’ in the column tools to fix this.