Free tool

Power BI date dimension

A paste-ready Power Query M date table for Power BI. One blank query gives you calendar and fiscal columns, relative offsets, a numeric DateKey, and reverse-sort keys so month and quarter labels sort the way the room expects.

Copy the M Download .txt

What it is and why it helps

Most Power BI models need a proper date table—not a calculated column bolted onto fact data. This Alluvium date dimension is a single M script you paste into Power Query. Set the year range and fiscal start once; you get Year, Month Name, Quarter, week boundaries, fiscal year/quarter/month, Day/Week/Month/Quarter/Year offsets vs today, Year-Month labels, and Sort By Column helpers for reverse chronological slicers.

Use it as the shared calendar behind measures, time intelligence, and filters so every report agrees on “this month” and “this fiscal year.”

Config knobs

Edit these near the top of the query before Close & Apply:

StartYear / EndYear
Inclusive calendar range. Defaults cover 2026–2030; widen to match your facts and forecast horizon.
FiscalYearStartMonth
Month number where the fiscal year begins (1 = January, 7 = July, and so on). Drives Fiscal Year, Fiscal Quarter, and Fiscal Month.
FirstDayOfWeek
Power Query day enum used for week-of-year, week-of-month, and start/end of week (for example Day.Monday or Day.Sunday).
DefaultSortAscending
When true, table rows sort oldest → newest by Date. Set false for newest → oldest as the default row order.

Install in five steps

In Power BI Desktop (or Excel Power Query), open Get data → Blank query (or Home → Transform data → New Source → Blank Query).
With the new query selected, open the Advanced Editor.
Delete the stub code, then paste the Alluvium Date Dimension M from the block below (or from the downloaded .txt).
Rename the query to something clear—for example Date or DimDate—and confirm no red underlines after Done.
Choose Close & Apply. Mark the table as a date table on the Date column, relate it to your facts, and set Sort By Column where needed.

DateKey (YYYYMMDD)

DateKey is an integer in YYYYMMDD form (for example 20260926). Use it for surrogate-key joins when facts store dates as integers, or keep relationships on the Date column when both sides are true dates.

Reverse-sort tip (Sort By Column)

Label columns like Month Name do not sort chronologically on their own. The M includes reverse-sort helpers called out in the script comments. In Model view, select the label column → Sort by column → pick the matching reverse-sort column when you want newest-first slicers (for example Month Name → Month Reverse Sort, and the same pattern for Year, Quarter, Week of Year, Day of Week, and Year-Month).

Power Query M — paste ready

Copy the block below into Advanced Editor, or download the .txt for your repo / handoff pack.

// Alluvium — Date Dimension (Power Query M)
// Drop into a blank query. Set StartYear / EndYear / fiscal start / week start below.
// Reverse-sort columns: use as Sort By Column on the matching label (e.g. Month Name → Month Reverse Sort).
let
    // --- config ---
    Today = Date.From(DateTime.LocalNow()),
    StartYear = 2026,
    EndYear = 2030,
    FiscalYearStartMonth = 1,          // 1 = Jan, 7 = Jul, etc.
    FirstDayOfWeek = Day.Monday,       // Day.Monday | Day.Sunday | …
    DefaultSortAscending = true,       // false = table rows newest → oldest
    // --- range ---
    StartDate = #date(StartYear, 1, 1),
    EndDate = #date(EndYear, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    Base = Table.TransformColumnTypes(
        Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
        {{"Date", type date}}
    ),
    WithDateKey = Table.AddColumn(
        Base,
        "DateKey",
        each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]),
        Int64.Type
    ),
    // --- calendar ---
    WithYear = Table.AddColumn(WithDateKey, "Year", each Date.Year([Date]), Int64.Type),
    WithYearStart = Table.AddColumn(WithYear, "Start of Year", each Date.StartOfYear([Date]), type date),
    WithYearEnd = Table.AddColumn(WithYearStart, "End of Year", each Date.EndOfYear([Date]), type date),
    WithMonth = Table.AddColumn(WithYearEnd, "Month", each Date.Month([Date]), Int64.Type),
    WithMonthStart = Table.AddColumn(WithMonth, "Start of Month", each Date.StartOfMonth([Date]), type date),
    WithMonthEnd = Table.AddColumn(WithMonthStart, "End of Month", each Date.EndOfMonth([Date]), type date),
    WithDaysInMonth = Table.AddColumn(WithMonthEnd, "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    WithDay = Table.AddColumn(WithDaysInMonth, "Day", each Date.Day([Date]), Int64.Type),
    WithDayName = Table.AddColumn(WithDay, "Day Name", each Date.DayOfWeekName([Date]), type text),
    WithDayOfWeek = Table.AddColumn(WithDayName, "Day of Week", each Date.DayOfWeek([Date], FirstDayOfWeek), Int64.Type),
    WithDayOfYear = Table.AddColumn(WithDayOfWeek, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    WithMonthName = Table.AddColumn(WithDayOfYear, "Month Name", each Date.MonthName([Date]), type text),
    WithQuarter = Table.AddColumn(WithMonthName, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    WithQuarterStart = Table.AddColumn(WithQuarter, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    WithQuarterEnd = Table.AddColumn(WithQuarterStart, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    WithWeekOfYear = Table.AddColumn(WithQuarterEnd, "Week of Year", each Date.WeekOfYear([Date], FirstDayOfWeek), Int64.Type),
    WithWeekOfMonth = Table.AddColumn(WithWeekOfYear, "Week of Month", each Date.WeekOfMonth([Date], FirstDayOfWeek), Int64.Type),
    WithWeekStart = Table.AddColumn(WithWeekOfMonth, "Start of Week", each Date.StartOfWeek([Date], FirstDayOfWeek), type date),
    WithWeekEnd = Table.AddColumn(WithWeekStart, "End of Week", each Date.EndOfWeek([Date], FirstDayOfWeek), type date),
    // --- fiscal ---
    FiscalShift = let
        raw = 13 - FiscalYearStartMonth,
        capped = if raw >= 12 or raw < 0 then 0 else raw
    in
        capped,
    WithFiscalBase = Table.TransformColumnTypes(
        Table.AddColumn(WithWeekEnd, "FiscalBaseDate", each Date.AddMonths([Date], FiscalShift), type date),
        {{"FiscalBaseDate", type date}}
    ),
    WithFiscalYear = Table.AddColumn(WithFiscalBase, "Fiscal Year", each Date.Year([FiscalBaseDate]), Int64.Type),
    WithFiscalQuarter = Table.AddColumn(WithFiscalYear, "Fiscal Quarter", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    WithFiscalMonth = Table.AddColumn(WithFiscalQuarter, "Fiscal Month", each Date.Month([FiscalBaseDate]), Int64.Type),
    DropFiscalBase = Table.RemoveColumns(WithFiscalMonth, {"FiscalBaseDate"}),
    // --- offsets vs Today ---
    WithDayOffset = Table.TransformColumnTypes(
        Table.AddColumn(DropFiscalBase, "Day Offset", each Duration.Days([Date] - Today), Int64.Type),
        {{"Day Offset", Int64.Type}}
    ),
    WithWeekOffset = Table.TransformColumnTypes(
        Table.AddColumn(
            WithDayOffset,
            "Week Offset",
            each
                let
                    thisStart = Date.StartOfWeek([Date], FirstDayOfWeek),
                    todayStart = Date.StartOfWeek(Today, FirstDayOfWeek)
                in
                    Number.IntegerDivide(Duration.Days(thisStart - todayStart), 7),
            Int64.Type
        ),
        {{"Week Offset", Int64.Type}}
    ),
    WithMonthOffset = Table.TransformColumnTypes(
        Table.AddColumn(
            WithWeekOffset,
            "Month Offset",
            each ([Year] - Date.Year(Today)) * 12 + ([Month] - Date.Month(Today)),
            Int64.Type
        ),
        {{"Month Offset", Int64.Type}}
    ),
    WithQuarterOffset = Table.TransformColumnTypes(
        Table.AddColumn(
            WithMonthOffset,
            "Quarter Offset",
            each ([Year] - Date.Year(Today)) * 4 + ([Quarter] - Date.QuarterOfYear(Today)),
            Int64.Type
        ),
        {{"Quarter Offset", Int64.Type}}
    ),
    WithYearOffset = Table.TransformColumnTypes(
        Table.AddColumn(WithQuarterOffset, "Year Offset", each [Year] - Date.Year(Today), Int64.Type),
        {{"Year Offset", Int64.Type}}
    ),
    // --- labels ---
    WithYearMonth = Table.AddColumn(WithYearOffset, "Year-Month", each Date.ToText([Date], "MMM yyyy"), type text),
    WithYearMonthCode = Table.TransformColumnTypes(
        Table.AddColumn(WithYearMonth, "Year-Month Code", each [Year] * 100 + [Month], Int64.Type),
        {{"Year-Month Code", Int64.Type}}
    ),
    WithYearQuarter = Table.AddColumn(
        WithYearMonthCode,
        "Year-Quarter",
        each Text.From([Year]) & " Q" & Text.From([Quarter]),
        type text
    ),
    // --- reverse-sort keys ---
    WithRevDate = Table.TransformColumnTypes(
        Table.AddColumn(WithYearQuarter, "Date Reverse Sort", each Number.From(EndDate) - Number.From([Date]), Int64.Type),
        {{"Date Reverse Sort", Int64.Type}}
    ),
    WithRevYear = Table.TransformColumnTypes(
        Table.AddColumn(WithRevDate, "Year Reverse Sort", each EndYear - [Year], Int64.Type),
        {{"Year Reverse Sort", Int64.Type}}
    ),
    WithRevMonth = Table.TransformColumnTypes(
        Table.AddColumn(WithRevYear, "Month Reverse Sort", each 13 - [Month], Int64.Type),
        {{"Month Reverse Sort", Int64.Type}}
    ),
    WithRevQuarter = Table.TransformColumnTypes(
        Table.AddColumn(WithRevMonth, "Quarter Reverse Sort", each 5 - [Quarter], Int64.Type),
        {{"Quarter Reverse Sort", Int64.Type}}
    ),
    WithRevWeek = Table.TransformColumnTypes(
        Table.AddColumn(WithRevQuarter, "Week of Year Reverse Sort", each 54 - [#"Week of Year"], Int64.Type),
        {{"Week of Year Reverse Sort", Int64.Type}}
    ),
    WithRevDayOfWeek = Table.TransformColumnTypes(
        Table.AddColumn(WithRevWeek, "Day of Week Reverse Sort", each 7 - [#"Day of Week"], Int64.Type),
        {{"Day of Week Reverse Sort", Int64.Type}}
    ),
    WithRevYearMonth = Table.TransformColumnTypes(
        Table.AddColumn(WithRevDayOfWeek, "Year-Month Reverse Sort", each (EndYear * 100 + 12) - [#"Year-Month Code"], Int64.Type),
        {{"Year-Month Reverse Sort", Int64.Type}}
    ),
    Sorted = Table.Sort(
        WithRevYearMonth,
        {{"Date", if DefaultSortAscending then Order.Ascending else Order.Descending}}
    )
in
    Sorted

Next step

Book a Session

If you want this date table wired into a trusted model—or a painful report rebuilt as a Quickstart—tell us what the room needs to decide.

Book a Session