Building a Dynamic Date Dimension Table in Power Query Using Pure M Code

If you've spent any time building data models in Power BI or Excel, you've run into the date dimension problem. Your sales table has a date column, your reporting needs week numbers, quarter labels, fiscal year flags, and "is this a weekday?" logic — and suddenly you're maintaining a static CSV of dates that someone created in 2019 and that suspiciously stops at December 31, 2024. When January 2025 rolls around, the whole model breaks.

The professional solution is a dynamic date dimension table — one that calculates itself automatically based on your actual data, requires zero maintenance, and extends forward and backward as your data changes. Power Query's M language is the perfect tool for building this, and once you understand the underlying mechanics, you'll be able to generate a date table that's genuinely production-ready: handling fiscal years, ISO week numbers, relative date flags, and more. You'll also understand why the M code works the way it does, not just what to copy and paste.

By the end of this lesson, you'll have a fully functional, self-updating date dimension table built entirely in M code — no DAX CALENDAR(), no static CSV, no manual updates required.

What you'll learn:

• How to dynamically derive your date range from existing data tables
• How to generate a contiguous list of dates using List.Dates() and convert it to a proper table
• How to build calculated columns for fiscal years, ISO week numbers, quarter labels, and relative date flags
• How to handle common M code pitfalls like type errors, null propagation, and culture-dependent formatting
• How to structure your query so it stays performant and maintainable

---

Prerequisites

You should be comfortable with the basics of Power Query: opening the Advanced Editor, understanding the let...in structure, and applying queries to a data model. You don't need to be an M expert, but you should know what a step is and how to reference a previous step. If you've built a few custom columns using the M formula bar, you're ready for this.

---

Why Build a Date Dimension in M Instead of DAX?

Before we write a single line of code, let's address the obvious question: Power BI has CALENDAR() and CALENDARAUTO() in DAX, Excel has its own ways to generate date ranges — why bother with M?

The answer has three parts.

First, M runs at load time, not query time. A DAX CALENDAR() table gets recalculated as part of your model's in-memory engine, but it's not a "real" table you can inspect, transform, or join on during the data preparation stage. An M-generated date table is a proper Power Query table — you can merge it, filter it, and apply any transformation you'd apply to data from a database.

Second, M gives you full control over dynamic range detection. With CALENDARAUTO(), Power BI scans your model for date columns and picks a range. That's convenient but unpredictable in complex models. With M, you explicitly say: "Start on the first day of the fiscal year containing the earliest date in my Sales table, and end on the last day of the fiscal year containing the latest date in that same table." That's a level of precision CALENDARAUTO() can't match.

Third, this approach is portable. An M-based date table works identically in Power BI, Excel Power Query, and Dataflows. The skills transfer across every tool in the Microsoft data stack.

---

Step 1: Establish Your Dynamic Date Range

The first thing your date dimension needs to know is its own boundaries. Hardcoding 2020-01-01 to 2030-12-31 defeats the entire purpose. Instead, you'll pull the minimum and maximum dates from your actual fact table — in this case, let's say it's a query called Sales with an OrderDate column.

Open a new blank query (Home > New Source > Blank Query) and open the Advanced Editor. Start with this:

let
    // Pull the actual date boundaries from your fact table
    MinDate = List.Min(Sales[OrderDate]),
    MaxDate = List.Max(Sales[OrderDate]),

    // Extend to full year boundaries so you don't get partial years
    StartDate = #date(Date.Year(MinDate), 1, 1),
    EndDate   = #date(Date.Year(MaxDate), 12, 31)
in
    EndDate

Run this just to verify it returns what you expect. If Sales[OrderDate] contains values from March 2021 through August 2024, StartDate should be 1/1/2021 and EndDate should be 12/31/2024.

Why extend to full year boundaries? If your earliest order is March 3, 2021 and you start your date table on that same day, you'll have an incomplete year. Any year-over-year comparison that needs January and February 2021 will silently return blanks. Always snap to year boundaries — or fiscal year boundaries, which we'll cover shortly.

Now let's think about what happens when Sales is empty or the OrderDate column contains null values. List.Min() on an empty list returns null, and Date.Year(null) will throw an error. Add a safety net:

let
    MinDate = List.Min(Sales[OrderDate]),
    MaxDate = List.Max(Sales[OrderDate]),

    // Default to current year if no data exists
    SafeMinDate = if MinDate = null then #date(Date.Year(DateTime.LocalNow()), 1, 1) else MinDate,
    SafeMaxDate = if MaxDate = null then #date(Date.Year(DateTime.LocalNow()), 12, 31) else MaxDate,

    StartDate = #date(Date.Year(SafeMinDate), 1, 1),
    EndDate   = #date(Date.Year(SafeMaxDate), 12, 31)
in
    EndDate

This is defensive programming that will save you from mysterious refresh failures in production.

---

Step 2: Generate the Date List and Convert to a Table

Now that you have your boundaries, use List.Dates() to generate every date in between. This function takes three arguments: a start date, a count of dates, and a duration.

let
    MinDate   = List.Min(Sales[OrderDate]),
    MaxDate   = List.Max(Sales[OrderDate]),
    SafeMin   = if MinDate = null then #date(Date.Year(DateTime.LocalNow()), 1, 1) else MinDate,
    SafeMax   = if MaxDate = null then #date(Date.Year(DateTime.LocalNow()), 12, 31) else MaxDate,
    StartDate = #date(Date.Year(SafeMin), 1, 1),
    EndDate   = #date(Date.Year(SafeMax), 12, 31),

    // Calculate the number of days between start and end (inclusive)
    DayCount  = Duration.Days(EndDate - StartDate) + 1,

    // Generate the list of dates
    DateList  = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),

    // Convert the list to a single-column table
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),

    // Set the correct data type
    TypedTable = Table.TransformColumnTypes(DateTable, {{"Date", type date}})
in
    TypedTable

At this point you have a table with one column, Date, containing a row for every calendar day from January 1 of your earliest sales year through December 31 of your latest. This is your skeleton. Everything from here is adding columns.

Performance note: List.Dates() generates the entire date list in memory before converting it to a table. For typical date dimension ranges (5–20 years), this is fine — you're talking about a few thousand rows at most. This is not the query that will slow down your refresh.

---

Step 3: Add the Standard Calendar Columns

Now comes the substantive work. You'll add columns one at a time using Table.AddColumn(). The pattern is consistent: you name the new column, write a function that receives each row, and return the calculated value. Each step references the step before it, building the table progressively.

Let's add the core calendar attributes. Add this block after TypedTable in your let chain:

    // Year
    AddYear = Table.AddColumn(TypedTable, "Year", each Date.Year([Date]), Int64.Type),

    // Quarter number (1–4)
    AddQuarterNum = Table.AddColumn(AddYear, "QuarterNumber", each Date.QuarterOfYear([Date]), Int64.Type),

    // Quarter label like "Q1 2023"
    AddQuarterLabel = Table.AddColumn(AddQuarterNum, "QuarterLabel",
        each "Q" & Text.From(Date.QuarterOfYear([Date])) & " " & Text.From(Date.Year([Date])),
        type text),

    // Month number (1–12)
    AddMonthNum = Table.AddColumn(AddQuarterLabel, "MonthNumber", each Date.Month([Date]), Int64.Type),

    // Full month name like "January"
    AddMonthName = Table.AddColumn(AddMonthNum, "MonthName",
        each Date.ToText([Date], "MMMM", "en-US"),
        type text),

    // Short month name like "Jan"
    AddMonthShort = Table.AddColumn(AddMonthName, "MonthShortName",
        each Date.ToText([Date], "MMM", "en-US"),
        type text),

    // Year-Month label like "2023-04" — useful for sorting
    AddYearMonth = Table.AddColumn(AddMonthShort, "YearMonthKey",
        each Text.From(Date.Year([Date])) & "-" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0"),
        type text),

    // Day of month (1–31)
    AddDayOfMonth = Table.AddColumn(AddYearMonth, "DayOfMonth", each Date.Day([Date]), Int64.Type),

    // Day of week number (1 = Monday in ISO, but M uses 0 = Sunday by default)
    // We'll normalize to 1 = Monday
    AddDayOfWeekNum = Table.AddColumn(AddDayOfMonth, "DayOfWeekNumber",
        each if Date.DayOfWeek([Date], Day.Monday) + 1 = 8 then 1
             else Date.DayOfWeek([Date], Day.Monday) + 1,
        Int64.Type),

    // Day name like "Monday"
    AddDayName = Table.AddColumn(AddDayOfWeekNum, "DayName",
        each Date.ToText([Date], "dddd", "en-US"),
        type text),

    // Short day name like "Mon"
    AddDayShort = Table.AddColumn(AddDayName, "DayShortName",
        each Date.ToText([Date], "ddd", "en-US"),
        type text),

    // Is it a weekday?
    AddIsWeekday = Table.AddColumn(AddDayShort, "IsWeekday",
        each if Date.DayOfWeek([Date], Day.Monday) < 5 then true else false,
        type logical),

    // Integer date key in YYYYMMDD format — handy for joining to fact tables
    AddDateKey = Table.AddColumn(AddIsWeekday, "DateKey",
        each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]),
        Int64.Type)

Always specify the culture parameter in Date.ToText(). Without "en-US" (or your appropriate locale), the output depends on the machine running the refresh. On a server configured for a different locale, your month names could come out in a different language without any warning. Lock the culture explicitly and you'll never get surprised.

Notice the DayOfWeekNumber logic. M's Date.DayOfWeek() returns 0 for Sunday by default, which is the American convention. By passing Day.Monday as the optional second argument, you get 0 for Monday — the ISO standard. We then add 1 to make it 1-based, which is what most reporting tools expect (1 = Monday, 7 = Sunday).

---

Step 4: Add Fiscal Year Logic

This is where generic date table tutorials usually stop, and where real-world reporting actually starts. Most organizations don't run on calendar years. Let's build fiscal year logic for a company whose fiscal year starts on July 1 — a very common setup in education, government, and non-profits.

The key insight: if a date's calendar month is July or later, it belongs to the fiscal year that starts that same calendar year. If the month is before July, it belongs to the fiscal year that started the previous calendar year.

    // Fiscal year start month — change this to parameterize the whole system
    FiscalYearStartMonth = 7,

    AddFiscalYear = Table.AddColumn(AddDateKey, "FiscalYear",
        each if Date.Month([Date]) >= FiscalYearStartMonth
             then Date.Year([Date]) + 1
             else Date.Year([Date]),
        Int64.Type),

Wait — why + 1? The convention here is to label a fiscal year by the calendar year in which it ends. So July 2023 through June 2024 is "FY2024." If your organization labels by start year instead, remove the + 1. Get this convention right before you deploy anything, because it affects every label downstream.

    // Fiscal quarter (1–4 within the fiscal year)
    AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "FiscalQuarterNumber",
        each
            let
                // How many months since the fiscal year started?
                MonthOffset = Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12)
            in
                Number.IntegerDivide(MonthOffset, 3) + 1,
        Int64.Type),

    // Fiscal quarter label like "FQ2 FY2024"
    AddFiscalQuarterLabel = Table.AddColumn(AddFiscalQuarter, "FiscalQuarterLabel",
        each "FQ" & Text.From([FiscalQuarterNumber]) & " FY" & Text.From([FiscalYear]),
        type text),

    // Fiscal month (1–12, where 1 = first month of fiscal year)
    AddFiscalMonth = Table.AddColumn(AddFiscalQuarterLabel, "FiscalMonthNumber",
        each Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1,
        Int64.Type)

The Number.Mod(...+ 12, 12) pattern is the standard trick for wrapping month arithmetic without going negative. If the fiscal year starts in July (month 7) and you're looking at January (month 1): 1 - 7 + 12 = 6, Mod(6, 12) = 6 — so January is the 7th fiscal month (0-indexed: 6, plus 1 = 7). Walk through a few dates mentally to convince yourself this works for any start month.

Make FiscalYearStartMonth a parameter. Define it as a separate step at the top of your query or, better yet, as a Power Query parameter. That way, if your organization's fiscal year changes (it happens), you change one value and every derived column updates automatically.

---

Step 5: Add ISO Week Numbers

ISO week numbers are a surprisingly contentious topic. The ISO 8601 standard says week 1 is the week containing the year's first Thursday. This means January 1 might be in week 52 or 53 of the previous year, and December 31 might be in week 1 of the following year. Get this wrong and your weekly reports will have mysterious gaps.

M doesn't have a built-in ISO week function, but you can calculate it accurately:

    // ISO Week Number — compliant with ISO 8601
    AddISOWeek = Table.AddColumn(AddFiscalMonth, "ISOWeekNumber",
        each
            let
                // Find the nearest Thursday — ISO weeks are anchored to Thursday
                DayOfWeek     = Date.DayOfWeek([Date], Day.Monday), // 0 = Mon, 6 = Sun
                NearestThursday = Date.AddDays([Date], 3 - DayOfWeek),
                // Week 1 contains January 4 (first Thursday-anchored week of the year)
                Jan4          = #date(Date.Year(NearestThursday), 1, 4),
                StartOfWeek1  = Date.AddDays(Jan4, -Date.DayOfWeek(Jan4, Day.Monday)),
                WeekNum       = Number.IntegerDivide(Duration.Days(NearestThursday - StartOfWeek1), 7) + 1
            in
                WeekNum,
        Int64.Type),

    // ISO Year (the year the ISO week belongs to — may differ from calendar year)
    AddISOYear = Table.AddColumn(AddISOWeek, "ISOWeekYear",
        each
            let
                DayOfWeek       = Date.DayOfWeek([Date], Day.Monday),
                NearestThursday = Date.AddDays([Date], 3 - DayOfWeek)
            in
                Date.Year(NearestThursday),
        Int64.Type),

    // Combined ISO Year-Week label like "2024-W03"
    AddISOWeekLabel = Table.AddColumn(AddISOYear, "ISOWeekLabel",
        each Text.From([ISOWeekYear]) & "-W" & Text.PadStart(Text.From([ISOWeekNumber]), 2, "0"),
        type text)

The logic here is elegant: any date's ISO week is determined by finding the Thursday of that date's week. Whatever calendar year that Thursday belongs to is the ISO year. Then we count how many complete weeks have passed since the start of week 1.

---

Step 6: Add Relative Date Flags

Relative date flags are what make a date dimension truly useful for dashboards. "Is this date in the current month?" "Is this the prior year?" These flags let report authors filter with a single checkbox rather than writing complex DAX.

    // Today's date — evaluated at refresh time
    Today = Date.From(DateTime.LocalNow()),

    AddIsToday = Table.AddColumn(AddISOWeekLabel, "IsToday",
        each [Date] = Today,
        type logical),

    AddIsCurrentWeek = Table.AddColumn(AddIsToday, "IsCurrentWeek",
        each
            let
                DayOfWeek = Date.DayOfWeek(Today, Day.Monday),
                WeekStart = Date.AddDays(Today, -DayOfWeek),
                WeekEnd   = Date.AddDays(WeekStart, 6)
            in
                [Date] >= WeekStart and [Date] <= WeekEnd,
        type logical),

    AddIsCurrentMonth = Table.AddColumn(AddIsCurrentWeek, "IsCurrentMonth",
        each Date.Year([Date]) = Date.Year(Today) and Date.Month([Date]) = Date.Month(Today),
        type logical),

    AddIsCurrentQuarter = Table.AddColumn(AddIsCurrentMonth, "IsCurrentQuarter",
        each Date.Year([Date]) = Date.Year(Today)
             and Date.QuarterOfYear([Date]) = Date.QuarterOfYear(Today),
        type logical),

    AddIsCurrentYear = Table.AddColumn(AddIsCurrentQuarter, "IsCurrentYear",
        each Date.Year([Date]) = Date.Year(Today),
        type logical),

    AddIsPriorYear = Table.AddColumn(AddIsCurrentYear, "IsPriorYear",
        each Date.Year([Date]) = Date.Year(Today) - 1,
        type logical),

    // Rolling 30/60/90-day flags from today
    AddIsLast30 = Table.AddColumn(AddIsPriorYear, "IsLast30Days",
        each [Date] >= Date.AddDays(Today, -30) and [Date] <= Today,
        type logical),

    AddIsLast90 = Table.AddColumn(AddIsLast30, "IsLast90Days",
        each [Date] >= Date.AddDays(Today, -90) and [Date] <= Today,
        type logical)

Warning: These relative flags are calculated at refresh time, not at query time. That means IsToday will be true for yesterday if your data refreshes at 11 PM and someone views the report at midnight. If your reports are time-sensitive, make sure your refresh schedule aligns with user expectations. Alternatively, add a note to your documentation — but don't silently ship behavior that might surprise stakeholders.

---

Step 7: Finalize and Clean Up

Now you've got a rich table with 20+ columns. The last step is to clean up column order, apply final types, and give the query a proper name.

    // Reorder columns for readability
    FinalTable = Table.ReorderColumns(AddIsLast90, {
        "DateKey", "Date", "Year", "QuarterNumber", "QuarterLabel",
        "MonthNumber", "MonthName", "MonthShortName", "YearMonthKey",
        "DayOfMonth", "DayOfWeekNumber", "DayName", "DayShortName",
        "IsWeekday", "FiscalYear", "FiscalQuarterNumber", "FiscalQuarterLabel",
        "FiscalMonthNumber", "ISOWeekNumber", "ISOWeekYear", "ISOWeekLabel",
        "IsToday", "IsCurrentWeek", "IsCurrentMonth", "IsCurrentQuarter",
        "IsCurrentYear", "IsPriorYear", "IsLast30Days", "IsLast90Days"
    })

in
    FinalTable

Name this query DimDate in the query pane. In Power BI, make sure to mark it as a date table (right-click the table in the model view and select "Mark as date table," then choose the Date column). This unlocks DAX time intelligence functions and tells the engine this is an authoritative date dimension.

---

The Complete Query

Here's everything assembled into one copy-paste-ready block:

let
    // ── Dynamic Range Detection ──────────────────────────────────────────
    MinDate   = List.Min(Sales[OrderDate]),
    MaxDate   = List.Max(Sales[OrderDate]),
    SafeMin   = if MinDate = null then #date(Date.Year(DateTime.LocalNow()), 1, 1) else MinDate,
    SafeMax   = if MaxDate = null then #date(Date.Year(DateTime.LocalNow()), 12, 31) else MaxDate,
    StartDate = #date(Date.Year(SafeMin), 1, 1),
    EndDate   = #date(Date.Year(SafeMax), 12, 31),

    // ── Date List Generation ─────────────────────────────────────────────
    DayCount  = Duration.Days(EndDate - StartDate) + 1,
    DateList  = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    TypedTable = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),

    // ── Standard Calendar Columns ─────────────────────────────────────────
    AddYear            = Table.AddColumn(TypedTable, "Year", each Date.Year([Date]), Int64.Type),
    AddQuarterNum      = Table.AddColumn(AddYear, "QuarterNumber", each Date.QuarterOfYear([Date]), Int64.Type),
    AddQuarterLabel    = Table.AddColumn(AddQuarterNum, "QuarterLabel",
                             each "Q" & Text.From(Date.QuarterOfYear([Date])) & " " & Text.From(Date.Year([Date])), type text),
    AddMonthNum        = Table.AddColumn(AddQuarterLabel, "MonthNumber", each Date.Month([Date]), Int64.Type),
    AddMonthName       = Table.AddColumn(AddMonthNum, "MonthName", each Date.ToText([Date], "MMMM", "en-US"), type text),
    AddMonthShort      = Table.AddColumn(AddMonthName, "MonthShortName", each Date.ToText([Date], "MMM", "en-US"), type text),
    AddYearMonth       = Table.AddColumn(AddMonthShort, "YearMonthKey",
                             each Text.From(Date.Year([Date])) & "-" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0"), type text),
    AddDayOfMonth      = Table.AddColumn(AddYearMonth, "DayOfMonth", each Date.Day([Date]), Int64.Type),
    AddDayOfWeekNum    = Table.AddColumn(AddDayOfMonth, "DayOfWeekNumber",
                             each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type),
    AddDayName         = Table.AddColumn(AddDayOfWeekNum, "DayName", each Date.ToText([Date], "dddd", "en-US"), type text),
    AddDayShort        = Table.AddColumn(AddDayName, "DayShortName", each Date.ToText([Date], "ddd", "en-US"), type text),
    AddIsWeekday       = Table.AddColumn(AddDayShort, "IsWeekday",
                             each Date.DayOfWeek([Date], Day.Monday) < 5, type logical),
    AddDateKey         = Table.AddColumn(AddIsWeekday, "DateKey",
                             each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]), Int64.Type),

    // ── Fiscal Year Columns ───────────────────────────────────────────────
    FiscalYearStartMonth  = 7,
    AddFiscalYear         = Table.AddColumn(AddDateKey, "FiscalYear",
                                each if Date.Month([Date]) >= FiscalYearStartMonth
                                     then Date.Year([Date]) + 1
                                     else Date.Year([Date]), Int64.Type),
    AddFiscalQuarter      = Table.AddColumn(AddFiscalYear, "FiscalQuarterNumber",
                                each Number.IntegerDivide(
                                    Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12), 3) + 1,
                                Int64.Type),
    AddFiscalQuarterLabel = Table.AddColumn(AddFiscalQuarter, "FiscalQuarterLabel",
                                each "FQ" & Text.From([FiscalQuarterNumber]) & " FY" & Text.From([FiscalYear]),
                                type text),
    AddFiscalMonth        = Table.AddColumn(AddFiscalQuarterLabel, "FiscalMonthNumber",
                                each Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1,
                                Int64.Type),

    // ── ISO Week Columns ──────────────────────────────────────────────────
    AddISOWeek = Table.AddColumn(AddFiscalMonth, "ISOWeekNumber",
        each
            let
                DayOfWeek       = Date.DayOfWeek([Date], Day.Monday),
                NearestThursday = Date.AddDays([Date], 3 - DayOfWeek),
                Jan4            = #date(Date.Year(NearestThursday), 1, 4),
                StartOfWeek1    = Date.AddDays(Jan4, -Date.DayOfWeek(Jan4, Day.Monday)),
                WeekNum         = Number.IntegerDivide(Duration.Days(NearestThursday - StartOfWeek1), 7) + 1
            in WeekNum,
        Int64.Type),
    AddISOYear = Table.AddColumn(AddISOWeek, "ISOWeekYear",
        each
            let
                DayOfWeek       = Date.DayOfWeek([Date], Day.Monday),
                NearestThursday = Date.AddDays([Date], 3 - DayOfWeek)
            in Date.Year(NearestThursday),
        Int64.Type),
    AddISOWeekLabel = Table.AddColumn(AddISOYear, "ISOWeekLabel",
        each Text.From([ISOWeekYear]) & "-W" & Text.PadStart(Text.From([ISOWeekNumber]), 2, "0"),
        type text),

    // ── Relative Date Flags ───────────────────────────────────────────────
    Today               = Date.From(DateTime.LocalNow()),
    AddIsToday          = Table.AddColumn(AddISOWeekLabel, "IsToday", each [Date] = Today, type logical),
    AddIsCurrentWeek    = Table.AddColumn(AddIsToday, "IsCurrentWeek",
                              each
                                  let WeekStart = Date.AddDays(Today, -Date.DayOfWeek(Today, Day.Monday)),
                                      WeekEnd   = Date.AddDays(WeekStart, 6)
                                  in [Date] >= WeekStart and [Date] <= WeekEnd,
                              type logical),
    AddIsCurrentMonth   = Table.AddColumn(AddIsCurrentWeek, "IsCurrentMonth",
                              each Date.Year([Date]) = Date.Year(Today) and Date.Month([Date]) = Date.Month(Today),
                              type logical),
    AddIsCurrentQuarter = Table.AddColumn(AddIsCurrentMonth, "IsCurrentQuarter",
                              each Date.Year([Date]) = Date.Year(Today)
                                   and Date.QuarterOfYear([Date]) = Date.QuarterOfYear(Today),
                              type logical),
    AddIsCurrentYear    = Table.AddColumn(AddIsCurrentQuarter, "IsCurrentYear",
                              each Date.Year([Date]) = Date.Year(Today), type logical),
    AddIsPriorYear      = Table.AddColumn(AddIsCurrentYear, "IsPriorYear",
                              each Date.Year([Date]) = Date.Year(Today) - 1, type logical),
    AddIsLast30         = Table.AddColumn(AddIsPriorYear, "IsLast30Days",
                              each [Date] >= Date.AddDays(Today, -30) and [Date] <= Today, type logical),
    AddIsLast90         = Table.AddColumn(AddIsLast30, "IsLast90Days",
                              each [Date] >= Date.AddDays(Today, -90) and [Date] <= Today, type logical),

    // ── Final Cleanup ─────────────────────────────────────────────────────
    FinalTable = Table.ReorderColumns(AddIsLast90, {
        "DateKey", "Date", "Year", "QuarterNumber", "QuarterLabel",
        "MonthNumber", "MonthName", "MonthShortName", "YearMonthKey",
        "DayOfMonth", "DayOfWeekNumber", "DayName", "DayShortName",
        "IsWeekday", "FiscalYear", "FiscalQuarterNumber", "FiscalQuarterLabel",
        "FiscalMonthNumber", "ISOWeekNumber", "ISOWeekYear", "ISOWeekLabel",
        "IsToday", "IsCurrentWeek", "IsCurrentMonth", "IsCurrentQuarter",
        "IsCurrentYear", "IsPriorYear", "IsLast30Days", "IsLast90Days"
    })

in
    FinalTable

---

Hands-On Exercise

Here's a realistic scenario to apply everything you've learned. Work through it yourself before checking the guidance below.

Scenario: Your company has two fact tables: Sales with an InvoiceDate column, and CustomerContracts with a ContractStartDate and ContractEndDate column. Your fiscal year starts on April 1. Your organization labels fiscal years by their ending calendar year (so April 2023–March 2024 is FY2024).

Your tasks:

1. Modify the dynamic range detection to span both fact tables — the date range should start at the minimum of all three date columns and end at the maximum.
2. Update FiscalYearStartMonth to 4.
3. Verify your fiscal year labeling is correct: for April 1, 2023, FiscalYear should be 2024.
4. Add a column called FiscalYearLabel that returns a text label like "FY2024" instead of the integer 2024.
5. Add an IsContractActive boolean flag that is true when the date falls within any active contract period. (Hint: this requires a different approach — you'll need to reference the CustomerContracts table inside your column expression.)

Guidance for task 1: You'll need to collect all date values into a single list before calling List.Min() and List.Max(). You can combine lists with the & operator:

AllDates = Sales[InvoiceDate] & CustomerContracts[ContractStartDate] & CustomerContracts[ContractEndDate],
MinDate  = List.Min(List.Select(AllDates, each _ <> null)),
MaxDate  = List.Max(List.Select(AllDates, each _ <> null))

The List.Select() call strips out null values before passing to List.Min() — safer than relying on the null-handling behavior of List.Min() alone.

Guidance for task 5: The IsContractActive column requires checking whether a date appears in any contract's date range. Inside a Table.AddColumn() expression, you can reference another query directly. Use List.AnyTrue() combined with Table.TransformColumns() or a more direct approach with Table.MatchesAnyRows():

AddIsContractActive = Table.AddColumn(AddIsLast90, "IsContractActive",
    each Table.MatchesAnyRows(
        CustomerContracts,
        (contract) => [Date] >= contract[ContractStartDate] and [Date] <= contract[ContractEndDate]
    ),
    type logical)

This works but will be slow for large contract tables because it scans the entire CustomerContracts table for every row in the date dimension. For production use with large tables, pre-aggregate the contracts into a date list and use a lookup approach instead.

---

Common Mistakes & Troubleshooting

"Expression.Error: We cannot convert the value null to type Date." This means List.Min() or List.Max() returned null — almost certainly because the source table is empty or the date column contains all nulls. Your SafeMin/SafeMax fallback logic should prevent this. If you're still seeing it, add a step that explicitly filters nulls: List.Select(Sales[OrderDate], each _ <> null).

Month names or day names are coming out in the wrong language. You forgot the culture parameter in Date.ToText(). Always pass "en-US" (or your target language) as the third argument. The M engine uses the locale of the machine running the refresh if you don't specify one — and on cloud refresh infrastructure, that can be anything.

Fiscal quarter numbers are wrong for edge months. Double-check your FiscalYearStartMonth value and trace through the Number.Mod(... + 12, 12) arithmetic manually. Write out the expected fiscal month for two or three test dates and compare to what your query produces. The most common mistake is an off-by-one error caused by 0-indexing (M returns 0 for the first month offset) versus 1-indexing (what your report consumers expect).

ISO week 53 appears even though you didn't expect it. This is correct behavior, not a bug. Some years have 53 ISO weeks — specifically, years where January 1 is a Thursday (and leap years where January 1 is a Wednesday). 2015, 2020, and 2026 all have 53 weeks. If your downstream reports treat week 53 as an error, that's a reporting logic problem, not a date table problem. Add a note to your data dictionary.

The query is extremely slow with a large Sales table. The dynamic range detection steps (List.Min(Sales[OrderDate])) cause Power Query to scan the entire Sales table at the beginning of the date dimension query's evaluation. If Sales is itself a slow query (many transformations, large dataset), this can cascade. Consider materializing the min/max dates as parameters updated on a schedule, or use query folding to push the MIN/MAX aggregation to the source database.

Table.MatchesAnyRows() for the IsContractActive flag brings the refresh to its knees. Yes — this is a nested loop with O(n × m) complexity. For a date table with 3,650 rows and a contract table with 10,000 rows, that's 36.5 million comparisons at refresh time. Generate a flat list of all dates covered by any contract instead:

ContractDates = List.Union(
    List.Transform(
        Table.ToRows(CustomerContracts),
        (row) => List.Dates(row{0}, Duration.Days(row{1} - row{0}) + 1, #duration(1,0,0,0))
    )
)

Then check membership with List.Contains(ContractDates, [Date]), which is O(n) in the date table size.

---

Summary & Next Steps

You've built a production-grade date dimension table in pure M code that:

• Automatically derives its date range from your fact tables
• Handles null values and empty source tables gracefully
• Includes standard calendar attributes with culture-safe formatting
• Supports configurable fiscal year logic with a single parameter change
• Calculates ISO 8601-compliant week numbers correctly, including years with 53 weeks
• Provides relative date flags that update automatically on every refresh
• Is structured to stay maintainable as requirements grow

The skills you've applied here — list generation, progressive table construction, nested let expressions inside column functions, and cross-query references — are the core toolkit of advanced M programming. You'll reach for these same patterns when building custom group-by logic, dynamic unpivoting, and parameterized data source queries.

Where to go from here:

• Convert FiscalYearStartMonth to a Power Query parameter. This makes your date table genuinely self-documenting and lets non-technical users adjust it without touching M code.
• Add holiday handling. Load a holiday calendar from a SharePoint list or static table, then add an IsHoliday column using a List.Contains() lookup. Combine it with IsWeekday to create a proper IsBusinessDay flag.
• Explore query folding. If your Sales table connects to a SQL database, the List.Min(Sales[OrderDate]) call may fold to a SELECT MIN(OrderDate) at the database level. Use the Query Diagnostics feature (Tools > Start Diagnostics) to verify whether folding is occurring and optimize accordingly.
• Parameterize the date range directly. Some models need a hard minimum date (you don't want your date table starting in 1995 because of one bad data entry). Build a query parameter for OverrideStartDate and incorporate it with an if expression in your range detection logic.

The date dimension is the foundation everything else in your model is joined to. Getting it right — dynamically, correctly, and maintainably — is one of the highest-leverage improvements you can make to any Power BI or Excel data model.