Date Table Generator
Set your fiscal year, weeks and holidays, check the rows, and copy a ready-made date table as DAX, Power Query (M) or a TMDL script. No Copilot needed.
Connect your model
Optional. With your model, tick the date columns your data uses, and the table can start and end with your data.
- In Power BI Desktop, open DAX query view, paste this query into a new tab and select Run.
- Select Copy above the results grid and paste below. Then tick the columns that hold real business dates.
Fiscal year and date range
The range should cover every date in your data, in whole years.
To see how far back and forward each column goes, and how many rows are blank, run this query. It covers the columns above, or every date column found in Step 1 if none are listed. Leave out columns whose earliest or latest date is a placeholder such as 1900-01-01 or 9999-12-31.
Weeks, weekends and holidays
These drive the week columns and Is Working Day.
Holidays in
| Date | Day | Holiday |
|---|
Choose the columns
Date is always included and marks the table as a date table. Text columns that need a sort order get one; a hidden number column is added when needed.
Check some rows
Exactly what the table will hold for these dates. Pick a date near a fiscal year end or a holiday to check them.
Copy the date table
DAX makes a calculated table; Power Query makes a query you can refresh and reuse. The TMDL script adds the table with its sort orders, formats and date-table setting in one go.