Data modelling

01The problem

You want revenue by month, and year-on-year comparisons. Grouping by order_date alone gets messy: months sort alphabetically (April, August, December…), there's nothing to group quarters by, and DAX's time functions (next lessons) need a complete calendar. The answer is a date table.

02The concept

Why a date table?

• One row per day, with no gaps, covering the whole period.
• Columns to group by: year, quarter, month name, month number.
• Required for reliable time intelligence in DAX (year-to-date, same period last year).

Build it with DAX (Modeling → New table):

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Year Month", FORMAT ( [Date], "yyyy-mm" )
)

Then:

1. Mark as date table: select the table → Table tools → Mark as date table → choose the Date column.
2. Relate Date[Date] (1) → orders[order_date] (*).
3. Sort by column: select Month → Column tools → Sort by column → Month Number. Now months sort January to December.

Tidy model habits

• Hide ID and technical columns report builders shouldn't use.
• Give tables and columns clear names (Revenue, not Sum of revenue2).
• Set formats once in the model (currency, thousands separators), not on every visual.
• Keep calculations as measures (next lessons) rather than many calculated columns.
• Turn off Auto date/time (File → Options and settings → Options → Current file → Data Load) once you have your own date table. It creates hidden date tables for every date column and bloats the file.

03Example

The finished model:

customers (1) ──* orders *── (1) products
                     *
                     │
                  (1) Date

Now Date[Year] and Date[Month] on a matrix, orders[revenue] in values, gives a correctly sorted month-by-year grid.

04Walkthrough

1. Modeling → New table, paste the DAX above, press Enter.
2. Mark it as a date table.
3. In Model view, drag Date[Date] onto orders[order_date]. Check it's one-to-many, single direction.
4. Sort Month by Month Number.
5. Build a Matrix: Rows Date[Year], then Date[Quarter]; Values orders[revenue]. Expand a year with the + icons.
6. Hide orders[order_date] in report view, so everyone uses Date instead.

05Practice

06Check your understanding