Power BI Course
Date and Time Transformations
Dates are the backbone of every business report — trend lines, year-over-year comparisons, rolling averages, and time intelligence all depend on dates being set up correctly. This lesson covers how to extract every useful component from a date column, how to build a complete Calendar table from scratch in Power Query, and the exact structure that Calendar table must have for DAX time intelligence functions to work reliably.
Why Dates Need Special Treatment
Extracting Date Components — The Transform Tab
Once a column has the Date type, the Date menu on the Transform tab and Add Column tab unlocks every extraction option through the UI. Use the Transform tab to replace the column with the extracted value; use the Add Column tab to add a new column while keeping the original date intact. Always use Add Column when extracting components — you almost always need to keep the source date for relationships.
Full Date Component Reference
Building a Calendar Table in Power Query
A Calendar table is a separate table containing one row for every date in your reporting range — every column being a date attribute. It is the foundation of all time intelligence in DAX. Without it, DATEYTD, SAMEPERIODLASTYEAR, TOTALYTD, and every other time intelligence function will not work. Build it once in Power Query, connect it to every fact table that has a date column, and all time intelligence becomes available everywhere.
// Complete Calendar table — paste into a blank query
// Home → New Source → Blank Query → Advanced Editor
let
// ── 1. Define the date range ───────────────────────────────
StartDate = #date(2022, 1, 1),
EndDate = #date(2026, 12, 31),
// ── 2. Generate a list of every date in the range ──────────
DateCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DateCount, #duration(1, 0, 0, 0)),
// ── 3. Convert list to a single-column table ────────────────
TableFromList = Table.FromList(
DateList,
Splitter.SplitByNothing(),
{"Date"},
null,
ExtraValues.Error
),
// ── 4. Set Date column type ─────────────────────────────────
ChangedType = Table.TransformColumnTypes(
TableFromList,
{{"Date", type date}}
),
// ── 5. Add calendar columns ─────────────────────────────────
AddYear = Table.AddColumn(ChangedType, "Year",
each Date.Year([Date]), Int64.Type),
AddMonthNum = Table.AddColumn(AddYear, "MonthNumber",
each Date.Month([Date]), Int64.Type),
AddMonthName = Table.AddColumn(AddMonthNum, "MonthName",
each Date.MonthName([Date]), type text),
AddMonthShort = Table.AddColumn(AddMonthName, "MonthShort",
each Date.ToText([Date], "MMM"), type text),
AddQuarter = Table.AddColumn(AddMonthShort, "Quarter",
each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
AddQuarterNum = Table.AddColumn(AddQuarter, "QuarterNumber",
each Date.QuarterOfYear([Date]), Int64.Type),
AddDayNum = Table.AddColumn(AddQuarterNum, "DayOfMonth",
each Date.Day([Date]), Int64.Type),
AddDayName = Table.AddColumn(AddDayNum, "DayName",
each Date.DayOfWeekName([Date]), type text),
AddDayOfWeek = Table.AddColumn(AddDayName, "DayOfWeek",
each Date.DayOfWeek([Date]), Int64.Type),
AddIsWeekend = Table.AddColumn(AddDayOfWeek, "IsWeekend",
each Date.DayOfWeek([Date]) >= 6, type logical),
AddWeekNum = Table.AddColumn(AddIsWeekend, "WeekOfYear",
each Date.WeekOfYear([Date]), Int64.Type),
AddYearMonth = Table.AddColumn(AddWeekNum, "YearMonthKey",
each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type),
AddYearMonthLabel = Table.AddColumn(AddYearMonth, "YearMonthLabel",
each Text.From(Date.Year([Date])) & "-"
& Text.PadStart(Text.From(Date.Month([Date])), 2, "0"),
type text),
// ── 6. Fiscal year (April 1 start) ──────────────────────────
AddFiscalYear = Table.AddColumn(AddYearMonthLabel, "FiscalYear",
each if Date.Month([Date]) >= 4
then "FY" & Text.From(Date.Year([Date]))
else "FY" & Text.From(Date.Year([Date]) - 1),
type text),
AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "FiscalQuarter",
each let m = Date.Month([Date]),
fq = if m >= 4 and m <= 6 then 1
else if m >= 7 and m <= 9 then 2
else if m >= 10 and m <= 12 then 3
else 4
in "FQ" & Text.From(fq),
type text)
in
AddFiscalQuarter| Date | Year | MoNum | MonthName | Qtr | DayName | Wknd | YMKey | YMLabel | FiscalYear | FiscalQtr |
|---|---|---|---|---|---|---|---|---|---|---|
| 2024-01-01 | 2024 | 1 | January | Q1 | Monday | false | 202401 | 2024-01 | FY2023 | FQ4 |
| 2024-03-15 | 2024 | 3 | March | Q1 | Friday | false | 202403 | 2024-03 | FY2023 | FQ4 |
| 2024-04-01 | 2024 | 4 | April | Q2 | Monday | false | 202404 | 2024-04 | FY2024 | FQ1 |
| 2024-07-06 | 2024 | 7 | July | Q3 | Saturday | true | 202407 | 2024-07 | FY2024 | FQ2 |
| 2024-12-31 | 2024 | 12 | December | Q4 | Tuesday | false | 202412 | 2024-12 | FY2024 | FQ3 |
Making the Calendar Table Dynamic
A hard-coded end date means the Calendar table must be manually updated every year. A dynamic end date reads the maximum date from your fact table and automatically extends the Calendar to cover it — the table stays current with every refresh.
// Dynamic Calendar — start and end dates driven by the data
let
// Read min and max dates from the Orders fact table
// (Orders must already be a query in this file)
MinDate = List.Min(Orders[OrderDate]),
MaxDate = List.Max(Orders[OrderDate]),
// Extend to full calendar years
StartDate = #date(Date.Year(MinDate), 1, 1),
EndDate = #date(Date.Year(MaxDate), 12, 31),
// Rest of the Calendar query continues as before...
DateCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DateCount, #duration(1, 0, 0, 0)),
// ... remaining steps unchanged
in
DateList // replace with full query in practiceConnecting the Calendar to Fact Tables
After loading the Calendar table, go to Model View and draw a relationship from Calendar[Date] to Orders[OrderDate] — Many-to-One from Orders to Calendar. Every fact table with a date column gets its own relationship to the Calendar. This single Calendar table then acts as the bridge for all time intelligence across all fact tables.
Teacher's Note: The single most common Calendar table mistake is building it with a DateTime column instead of a Date column. The Calendar table has one row per date, with the time component always at midnight (00:00:00). If an Orders table has OrderDate as DateTime with "2024-01-15 09:42:00", it will never match the Calendar row for "2024-01-15 00:00:00" — and the relationship silently produces blank for every time intelligence calculation. Fix OrderDate to Date type in Power Query before building the relationship, not after.
Practice
Practice 1 of 3
The M function that generates a list of consecutive dates given a start date, a count of dates, and a step interval is List.___.
Practice 2 of 3
A Calendar table must contain a ___ sequence of dates with no gaps — skipping even a single date causes DAX time intelligence calculations to produce incorrect results for the period containing that gap.
Practice 3 of 3
In a fiscal year that starts on April 1, the month of January 2024 belongs to fiscal year ___.
Lesson Quiz
Quiz 1 of 3
You create a relationship between Calendar[Date] and Orders[OrderDate], but every time intelligence DAX measure returns blank. OrderDate is stored as DateTime in the Orders table. What is the cause and fix?
Quiz 2 of 3
Your Calendar table covers 2022-01-01 to 2024-12-31. In 2025 a new batch of orders arrives with dates in January 2025. What happens to those orders in time intelligence calculations, and how do you prevent it?
Quiz 3 of 3
You want a YearMonth column in the Calendar table for use as a sort key on a line chart axis. A colleague suggests using the text label "2024-03" while you suggest the integer 202403. Which is better for sorting and why?
Next up — Lesson 29 covers Query Parameters in Power Query, including how to create parameters that control file paths, date ranges, and filter values so a single report can be repointed to different data sources or time periods without editing any M code.