What tips and common mistakes are there?

- Check the start date of the fiscal year carefully, as this determines the correct calculation of fiscal weeks. - Use consistent date formats in your report to prevent errors in relationships. - Avoid duplicate date tables in your model to prevent confusion and performance issues. - Test your date table with various filters to make sure fiscal weeks are displayed correctly.

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

First create a base date table with the CALENDAR function. Then add a calculated column for the fiscal year using an IF formula based on the month. For the fiscal week, use a WEEKNUM formula on the difference between the date and the start date of the fiscal year. Assuming the fiscal year starts on 1 April: add the formula WEEKNUM(DateTable[Date] - DATE(FiscalYear, 4, 1) + 1, 2) as a calculated column.

Why do I use a separate date table instead of just the date column from my fact table?

A separate date table provides better performance and correct time intelligence in Power BI. With a dedicated date table you can reliably use functions such as TOTALYTD, SAMEPERIODLASTYEAR and DATESINPERIOD. Without a separate date table, dates may be missing from the sequence, causing calculations to be incorrect. Power BI only marks a table as a 'date table' when you explicitly set this, which is required for some time intelligence functions.

Power BI: Create a date table with fiscal weeks

A date table is essential for time analyses in Power BI, but standard date tables often do not include fiscal weeks. In this article you will learn how to create a date table that includes fiscal weeks, so your reports better align with your organisation's financial calendar.

What you need

A Power BI report with a dataset where you want to add a date table
Basic knowledge of DAX formulas in Power BI
Knowledge of your organisation’s fiscal year start date

Step 1: Create a base date table

The first step is to create a standard date table containing all dates within the desired period. This forms the basis for adding fiscal weeks.

In Power BI Desktop, enter the following DAX formula when creating a new table:

DateTable =
CALENDAR(DATE(2025,1,1), DATE(2025,12,31))

Adjust the dates to the desired period. This table now contains all dates from 1 January 2025 to 31 December 2025.

Step 2: Add columns for fiscal year and fiscal week

Now add columns that calculate the fiscal year and fiscal week, based on the start date of the fiscal year. Assuming the fiscal year starts on 1 April.

Add a new column with this DAX formula for the fiscal year:

FiscalYear = IF(MONTH(DateTable[Date]) >= 4, YEAR(DateTable[Date]), YEAR(DateTable[Date]) - 1)

For the fiscal week, use this formula:

FiscalWeek = WEEKNUM(DateTable[Date] - DATE(FiscalYear, 4, 1) + 1, 2)

This starts the week numbering from 1 April, with week 1 beginning on the first day of the fiscal year.

Step 3: Use the date table in your reports

Connect the date table to your fact tables via the date column. Use the fiscal year and fiscal week columns to create dynamic filters and slicers that align with the financial calendar.

For example, you can add a slicer on fiscal week to quickly create analyses per fiscal week, making the reports more relevant for financial reporting and budgeting.

Tips & common mistakes

Check the start date of the fiscal year carefully, as this determines the correct calculation of fiscal weeks.
Use consistent date formats in your report to prevent errors in relationships.
Avoid duplicate date tables in your model to prevent confusion and performance issues.
Test your date table with various filters to make sure fiscal weeks are displayed correctly.

Example / mini use case

A finance team wants to create quarterly reports that start in April. With this date table they can easily filter reports on fiscal weeks and fiscal years, giving them quick insight into performance per financial period. This makes the reporting process more efficient and better aligned with business operations.

Conclusion

With this approach you create a powerful date table with fiscal weeks that significantly improves your analyses in Power BI. Try it out yourself and discover how your reports become even more relevant. For this date table, work directly in Power BI Desktop or the online environment.

Our tip: Make the date table dynamic by linking the start and end date to the minimum and maximum values in your fact table. This way you never have to manually update the date table.