Downloads

Date table generator

Build a date table with calendar and fiscal columns, week numbers and working-day flags as Power Query M, DAX, T-SQL or MariaDB SQL.

4output languagesPower Query M, DAX, T-SQL, MariaDB
Anyfiscal year end12 start months, 3 label styles
Columns (Date, Year, Month Number, Day and Day of Week are always included)

Preview

The first 15 and last 5 rows, computed in your browser with the same rules as the code. SQL shows 1 and 0 where this table shows TRUE and FALSE.

Limits: the table covers whole calendar years, so the first and last fiscal years may be partial. Holidays are not included in Is Working Day, which only excludes Saturdays and Sundays. Keep the range to 50 years or fewer.

How it works

  1. Set the range

    Choose the first and last year and the month the fiscal year starts.

  2. Choose columns

    Add the quarter, week, fiscal and working-day columns you need.

  3. Copy the code

    Pick Power Query M, DAX, T-SQL or MariaDB and copy it. The preview shows the rows it makes.

Why a date table matters

Almost every finance question is a question about time: sales this quarter against last quarter, year to date revenue, receivables older than 60 days, budget for the month. A date table gives those questions one shared calendar. Each row is one day, and the columns say what that day is: the year, the quarter, the fiscal period, whether it is a weekend, whether it is the last day of the month.

Without one, each report builds its own calendar, and the reports disagree. Time intelligence functions in Power BI and time grouping in Excel need a complete calendar with no gaps. A date table that has every day, with no missing dates, makes running totals, year to date sums and comparisons with the same period last year work correctly.

How to use the output

Choose the years, the month the fiscal year starts, the day the week starts and the columns you need. Then copy the code for your tool. Power Query M creates the table inside a query. DAX creates a calculated table inside a Power BI model. T-SQL and MariaDB SQL create a result set you can save as a view, a table or a feed into a warehouse.

The fiscal logic works for any start month. A fiscal year is named for the calendar year in which it ends by default, so a year that runs from July 2026 to June 2027 is FY2027. Switch the naming option if your organization names it for the year it starts. A January start is the calendar year.

Mark it as the date table in Power BI

Load or create the table, then select it in the Data pane. On the Table tools tab, choose Mark as date table, and pick the Date column. Power BI then uses this table for time intelligence and checks that the dates are unique and have no gaps. Keep the Date column as a date type with no time part, and relate your fact table's date column to it.

Use it in Excel

In Excel, load the Power Query M query into the Data Model or onto a sheet. Relate the table to your data in the Power Pivot model with the Date column. Pivot tables then group by year, quarter and month from this table, and the fiscal columns give you the period labels your management reports use.

Frequently asked questions

What does a fiscal year named FY2027 mean?

With the default setting, FY2027 is the fiscal year that ends in 2027. For a July start, it runs from 1 July 2026 to 30 June 2027. Change the naming option if your company calls that year FY2026.

Why does the week number sometimes say 53 in late December?

Week 1 is the week that contains January 1, and weeks start on the day you choose. The rule restarts at each January 1, so the last few days of December can fall in a week that the next year counts as week 1. This is not the ISO week number.

Can I use this table with a fiscal year that starts in March?

Yes. Choose March as the start month. Fiscal periods then count from March as period 1, and the fiscal quarters start in March, June, September and December.

Does the table include public holidays?

No. Is Working Day excludes Saturdays and Sundays only. Add a holiday table and mark those days in a separate column if you need them.

Why do the SQL outputs use 1 and 0 instead of TRUE and FALSE?

SQL Server and MariaDB have no plain true and false columns for this use. The 1 and 0 values work as flags, and Power BI reads them as whole numbers.

Is my data sent anywhere?

No. The tool runs in your browser, and it stores nothing. The code it writes uses only date functions, so it does not read any data.

More tools: all tools · function reference · guides