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

The star schema from the last lesson has one gap: dates. orders[order_date] holds dates, but you can't group it by quarter, sort months properly or use DAX's time functions with it alone. Every serious Power BI model has a separate date table.

Why a date table?

A proper date table has:

• one row per day, with no gaps, covering whole years;
• a column of type Date that's unique;
• columns to group and sort by: year, quarter, month name, month number.

Building it with DAX

Modeling → New table, then:

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" )
)

Read it from the inside out:

Mark as date table

Select the table → Table tools → Mark as date table → choose the Date column. Power BI checks the column is unique with no gaps, and then uses it for time intelligence. If it refuses, the range has a gap or a duplicate.

Relate it to the fact table

In Model view, drag Date[Date] onto orders[order_date]: one-to-many, single direction, like the other dimensions. The model is now a full star: customers, products and Date around orders.

Sort by column

Text sorts alphabetically, so a Month column shows Apr, Aug, Dec, Feb, Jan… on a chart. Fix it once in the model:

1. Select Date[Month].
2. Column tools → Sort by column → Month Number.

Every visual that uses Month now shows Jan to Dec in order. The same trick works for anything with a natural order, such as size bands sorted by a band number.

Hierarchies

A hierarchy groups columns from big to small, so users can drill down: Year → Quarter → Month. Right-click Year → Create hierarchy, then drag Quarter and Month onto it. In a visual, the drill arrows move between the levels.

Tidy model habits

A model is used by everyone who builds reports on it, so keep it easy to read:

Auto date/time is under File → Options and settings → Options → Current file → Data Load.

03Example

The finished model is the full star from the last lesson's diagram: orders in the middle, customers, products and Date around it.

Now Date[Year] and Date[Quarter] on a matrix, orders[revenue] in values, gives revenue by quarter:

And with Date[Month] sorted by Month Number, a line chart of 2025 runs January to December in order, with the December peak (₦66,284,310) at the right end, where it belongs.

04Walkthrough

1. Modeling → New table, paste the DAX above, press Enter. The Date table appears. Open it in Table view and check the first row is 1 January 2025 and the last is 31 December 2026.
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. Create a hierarchy: Year → Quarter → Month.
6. Build a Matrix: Rows the hierarchy; Values orders[revenue]. Expand a year with the + icons.
7. Hide orders[order_date] in report view, so everyone uses Date instead.
8. Turn off Auto date/time for this file.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding