How to Create a Date Table in Power BI with DAX Step by Step

João Barros 08 de July de 2026 3 min read

A date table is the foundation of any time-based analysis in Power BI: without it, time intelligence functions — such as comparing sales year over year or calculating running totals — don't work reliably. The good news is that building a date table with DAX takes only a few minutes and then serves your entire model. You'll create it from scratch, connect it to your data, and mark it properly as a date table.

Prerequisites

Power BI Desktop installed (any recent version works).
A model with at least one fact table that has a date column — for example, Vendas[Data].
Basic DAX knowledge helps but isn't required: you'll just copy the code.

Step 1: Create the table with CALENDAR

On the Modeling tab, click New table. You'll use the CALENDAR function, which generates one row for every day between two dates, inside ADDCOLUMNS, which adds useful columns such as year, month, and quarter. Paste the code below and adjust the dates to match your data range:

Calendario = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2026,12,31)),
    "Ano", YEAR([Date]),
    "NumeroMes", MONTH([Date]),
    "Mes", FORMAT([Date], "mmmm"),
    "Trimestre", "T" & ROUNDUP(MONTH([Date])/3, 0),
    "AnoMes", FORMAT([Date], "yyyy-mm"),
    "DiaSemana", FORMAT([Date], "dddd")
)

The [Date] column is created automatically by CALENDAR; the others derive from it. Each column has a purpose: Ano and Trimestre are for grouping, AnoMes is great for sorting time series, and DiaSemana helps analyse weekly patterns. If you'd rather have the range adjust itself to the data, swap CALENDAR(...) for CALENDARAUTO(), which reads the model's minimum and maximum dates.

Step 2: Sort the month correctly

By default, Power BI sorts the Mes column alphabetically — April appears before January, which scrambles any chart. Select the Mes column, go to Column tools, and use Sort by column to sort it by the NumeroMes column. From then on, months always appear in chronological order, from January to December.

Step 3: Mark as a date table

This step is essential and many users forget it. With the table selected, go to Table tools and click Mark as Date Table; choose the [Date] column as the date column. This guarantees that time intelligence functions, such as TOTALYTD or SAMEPERIODLASTYEAR, rely on a continuous, correct calendar.

A date table must be continuous, with no missing days between the first and last date. The CALENDAR function ensures this automatically.

Step 4: Create the relationship with your data

Open the Model view and drag the [Date] field from the Calendario table onto the date column of your fact table — for example, Vendas[Data]. You get a one-to-many relationship, with the calendar on the "one" side. This link is what makes time filters propagate to every visual in the report.

Check the result

To confirm everything is right, create a simple measure and place it in a table next to the Calendario[AnoMes] field:

Vendas YTD = TOTALYTD(SUM(Vendas[Valor]), Calendario[Date])

If the values accumulate through the year and reset in January, your date table is working. Also check two details: that the months appear in chronological order, and that the relationship on the Model tab is active (shown as a solid line).

Conclusion

With a well-built date table marked as a date table, your model is ready for reliable, easy-to-maintain time analysis. The natural next step is to explore functions like SAMEPERIODLASTYEAR and DATEADD to compare periods with just a few lines of DAX. What other column would you find useful in your calendar — perhaps one to flag holidays or weekends?