Your finance team just told you their fiscal year ends on the last Saturday of September. Your retail client follows a 4-4-5 week pattern. Your international subsidiary uses ISO week numbering, while the US division insists on weeks starting Monday but reports starting Sunday. Welcome to the calendar dimension problem — the part of data modeling that looks simple in theory and turns into a three-day project in practice.
Most Power BI developers start with a basic date table: a list of dates, a day-of-week column, maybe a month name. That works fine until a stakeholder asks why the Q3 total doesn't match what Accounting produced, or why the week numbers in the report are off by one compared to the ERP system. The root cause is almost always a calendar dimension that wasn't built with enough precision. The good news is that Power Query M gives you everything you need to build a fully configurable, production-grade date dimension — one that handles fiscal periods, custom week rules, and holiday logic without requiring you to maintain a static Excel file.
By the end of this lesson, you'll have a complete, parameterized M function that generates a date dimension table on demand. You'll be able to swap fiscal year start months, configure week-start days, inject holiday lists from any source, and produce both calendar and fiscal period columns that match your organization's actual reporting logic.
What you'll learn:
You should be comfortable writing M expressions in the Advanced Editor, understand record and list operations at an intermediate level, and have used List.Generate or List.Dates before. Familiarity with table transformations (Table.AddColumn, Table.TransformColumnTypes) is assumed. You don't need to know anything about date dimension theory — we'll cover the relevant concepts as we go.
Before we add any intelligence, we need the raw list of dates. This sounds trivial, but the scaffolding you build here determines how clean the rest of the function will be.
Start a new blank query in Power BI Desktop or Excel and open the Advanced Editor. We'll build the date generator as a function from the start rather than writing a flat query and retrofitting it later.
(
StartDate as date,
EndDate as date,
FiscalYearStartMonth as number, // 1–12; 10 = October fiscal start
WeekStartDay as number, // 0 = Sunday, 1 = Monday
HolidayTable as nullable table // null if not used
) as table =>
let
// ── 1. Date Spine ────────────────────────────────────────────────────
DateCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DateCount, #duration(1,0,0,0)),
BaseTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
TypedTable = Table.TransformColumnTypes(BaseTable, {{"Date", type date}})
in
TypedTable
This is your skeleton. List.Dates does the heavy lifting of generating the sequence, and wrapping it in a function immediately means you're never tempted to hardcode the date range. Notice FiscalYearStartMonth, WeekStartDay, and HolidayTable are already in the signature even though we haven't used them yet — this forces you to think about the interface before you write the logic.
Why not use
#date(year, 1, 1)and#date(year, 12, 31)as defaults? Because fiscal year ranges frequently span calendar years. Your date spine needs to be wide enough to cover the full fiscal year on both ends. We'll let the caller decide, which is the correct design.
Let's add the baseline calendar columns. We'll use a Table.AddColumn chain with a helper approach — rather than repeating Table.AddColumn dozens of times, we'll build the columns in logical groups.
// ── 2. Base Calendar Attributes ──────────────────────────────────────
WithYear = Table.AddColumn(TypedTable, "CalendarYear", each Date.Year([Date]), Int64.Type),
WithQtr = Table.AddColumn(WithYear, "CalendarQuarterNum", each Date.QuarterOfYear([Date]), Int64.Type),
WithQtrLbl = Table.AddColumn(WithQtr, "CalendarQuarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
WithMonth = Table.AddColumn(WithQtrLbl, "MonthNum", each Date.Month([Date]), Int64.Type),
WithMonthNm = Table.AddColumn(WithMonth, "MonthName", each Date.ToText([Date], "MMMM"), type text),
WithMonthSh = Table.AddColumn(WithMonthNm, "MonthShort", each Date.ToText([Date], "MMM"), type text),
WithYrMo = Table.AddColumn(WithMonthSh, "YearMonth", each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type),
WithDay = Table.AddColumn(WithYrMo, "DayOfMonth", each Date.Day([Date]), Int64.Type),
WithDOWNum = Table.AddColumn(WithDay, "DayOfWeekNum_Sun0", each Date.DayOfWeek([Date], Day.Sunday), Int64.Type),
WithDOWNm = Table.AddColumn(WithDOWNum, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
WithDOYNum = Table.AddColumn(WithDOWNm, "DayOfYear", each Date.DayOfYear([Date]), Int64.Type),
WithDateKey = Table.AddColumn(WithDOYNum, "DateKey", each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]), Int64.Type)
A few design decisions worth calling out explicitly:
DayOfWeekNum_Sun0 encodes Sunday as 0. This is the M default and the Excel convention. We're keeping it in the output because downstream consumers may depend on it, but we'll use the configurable WeekStartDay parameter for our own week number calculations.
YearMonth as an integer (e.g., 202403 for March 2024) is useful as a sort key in visuals where month names need to sort chronologically, not alphabetically.
DateKey as YYYYMMDD integer is a standard surrogate for joining to fact tables that store dates as integers rather than proper date types.
This is where most date table implementations cut corners, and where you'll immediately separate your work from the generic templates floating around the internet.
The challenge is that "week number" has at least three common definitions in enterprise reporting:
We'll implement all three, using the WeekStartDay parameter for the custom fiscal week logic:
// ── 3. Week Numbering ────────────────────────────────────────────────
// Adjusted Day of Week (0 = WeekStartDay)
WithAdjDOW = Table.AddColumn(WithDateKey, "DayOfWeekAdj",
each Number.Mod(Date.DayOfWeek([Date], Day.Sunday) - WeekStartDay + 7, 7),
Int64.Type),
// US-style week number (week containing Jan 1 = Week 1, Sunday-start)
WithWkUS = Table.AddColumn(WithAdjDOW, "WeekNumUS",
each Date.WeekOfYear([Date], Day.Sunday),
Int64.Type),
// ISO 8601 week number
// ISO week: Monday start, week containing first Thursday = Week 1
// M doesn't have a native ISO week function, so we calculate it
WithISOWk = Table.AddColumn(WithWkUS, "ISOWeekNum",
each
let
d = [Date],
// Shift to the ISO Thursday of this week
DayOfWkMon = Date.DayOfWeek(d, Day.Monday), // 0=Mon
Thursday = Date.AddDays(d, 3 - DayOfWkMon),
ISOYear = Date.Year(Thursday),
Jan4 = #date(ISOYear, 1, 4), // Jan 4 is always in W1
Jan4Mon = Date.AddDays(Jan4, -Date.DayOfWeek(Jan4, Day.Monday)),
WeekNum = Duration.Days(Thursday - Jan4Mon) / 7 + 1
in
Number.RoundDown(WeekNum)
, Int64.Type),
// ISO Year (can differ from Calendar Year in early Jan / late Dec)
WithISOYr = Table.AddColumn(WithISOWk, "ISOWeekYear",
each
let
d = [Date],
DayOfWkMon = Date.DayOfWeek(d, Day.Monday),
Thursday = Date.AddDays(d, 3 - DayOfWkMon)
in
Date.Year(Thursday)
, Int64.Type),
// Combined ISO Year-Week label: "2024-W03"
WithISOLbl = Table.AddColumn(WithISOYr, "ISOWeekLabel",
each Text.From([ISOWeekYear]) & "-W" & Text.PadStart(Text.From([ISOWeekNum]), 2, "0"),
type text)
The ISO week calculation deserves an explanation because it trips up developers who try to implement it from scratch. The key insight is that the ISO week year is defined by where Thursday falls, not by the calendar date itself. December 31 can be in ISO week 1 of the following year if that Thursday lands in January. The Jan4 anchor point works because January 4th is always in ISO week 1 by definition — no matter what year configuration you have, the first Thursday of the year is on or before January 7th, and January 4th is always in that same week.
Watch out for December/January boundary dates.
ISOWeekYearandCalendarYearwill differ for dates like December 30–31 in some years, and January 1–3 in others. This is correct behavior. If you're joining to a fact table on ISO week, make sure you join on bothISOWeekYearandISOWeekNum, not just the week number.
Fiscal year logic is where organizations diverge most dramatically, so we need to be explicit about which model we're implementing. We'll handle the most common case: a fiscal year that starts on the first day of a given month (e.g., October 1 for a US government-style fiscal year, or April 1 for a UK financial year).
// ── 4. Fiscal Period Columns ──────────────────────────────────────────
// Fiscal Year: the calendar year in which the fiscal year ENDS
// Example: if FiscalYearStartMonth = 10 (October),
// Oct 2023 – Sep 2024 is Fiscal Year 2024
WithFY = Table.AddColumn(WithISOLbl, "FiscalYear",
each
if Date.Month([Date]) >= FiscalYearStartMonth
then Date.Year([Date]) + 1
else Date.Year([Date])
, Int64.Type),
// Edge case: if fiscal year starts in January, fiscal year = calendar year
// The formula above handles this correctly: Month >= 1 is always true,
// so FiscalYear = CalendarYear + 1... which is wrong for January start.
// We need a conditional:
WithFYFixed = Table.AddColumn(
Table.RemoveColumns(WithFY, "FiscalYear"),
"FiscalYear",
each
if FiscalYearStartMonth = 1
then Date.Year([Date])
else if Date.Month([Date]) >= FiscalYearStartMonth
then Date.Year([Date]) + 1
else Date.Year([Date])
, Int64.Type),
// Fiscal Quarter (1–4)
WithFQ = Table.AddColumn(WithFYFixed, "FiscalQuarterNum",
each
let
FiscalMonth = Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1
in
Number.RoundUp(FiscalMonth / 3)
, Int64.Type),
WithFQLbl = Table.AddColumn(WithFQ, "FiscalQuarter",
each "FQ" & Text.From([FiscalQuarterNum]),
type text),
// Fiscal Month Number (1 = first month of fiscal year)
WithFM = Table.AddColumn(WithFQLbl, "FiscalMonthNum",
each Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1,
Int64.Type),
// Fiscal Year-Quarter sort key: e.g., 20243 = FY2024 Q3
WithFYQ = Table.AddColumn(WithFM, "FiscalYearQuarter",
each [FiscalYear] * 10 + [FiscalQuarterNum],
Int64.Type),
// Fiscal Year-Month sort key
WithFYM = Table.AddColumn(WithFYQ, "FiscalYearMonth",
each [FiscalYear] * 100 + [FiscalMonthNum],
Int64.Type),
// Fiscal Year label: "FY2024"
WithFYLbl = Table.AddColumn(WithFYM, "FiscalYearLabel",
each "FY" & Text.From([FiscalYear]),
type text)
The Number.Mod pattern for fiscal month calculation is the core trick here. By subtracting FiscalYearStartMonth and applying modulo 12, you rotate the calendar so that the fiscal year start month maps to month 1, regardless of which calendar month it is. Let's verify with an example:
FiscalYearStartMonth = 10)Mod(10 - 10 + 12, 12) + 1 = Mod(12, 12) + 1 = 0 + 1 = 1 ✓ (fiscal month 1)Mod(9 - 10 + 12, 12) + 1 = Mod(11, 12) + 1 = 11 + 1 = 12 ✓ (fiscal month 12)Mod(1 - 10 + 12, 12) + 1 = Mod(3, 12) + 1 = 3 + 1 = 4 ✓ (fiscal month 4)The FY2024 "ends in" convention vs "starts in" convention. There's no universal standard. Some organizations label a fiscal year by when it starts (so Oct 2023 = FY2023). Others label it by when it ends (Oct 2023 = FY2024). The code above uses the "ends in" convention, which is standard in US government and most enterprise settings. Change
Date.Year([Date]) + 1toDate.Year([Date])if your organization uses "starts in."
Holiday integration is the feature that transforms a date table from a reporting convenience into a genuine business intelligence asset. Working days drive SLA calculations, payroll period logic, and operations scheduling — and they can't be computed without a holiday list.
The design pattern here is to accept HolidayTable as a nullable parameter. When it's null, we skip the holiday join. When it's provided, we expect it to have at minimum a single Date column of type date.
// ── 5. Holiday and Working Day Logic ────────────────────────────────
// Determine if each date is a weekend based on WeekStartDay config
// We define "weekend" as the 2 days before the week starts
WithIsWknd = Table.AddColumn(WithFYLbl, "IsWeekend",
each
let
DOW = Date.DayOfWeek([Date], Day.Sunday) // 0=Sun
in
// Standard Mon–Fri work week when WeekStartDay = 1
// Adjust if your work week is different
if WeekStartDay = 1
then (DOW = 0 or DOW = 6) // Sun or Sat
else if WeekStartDay = 0
then (DOW = 6 or DOW = 5) // Sat or Fri (Sun start = Fri/Sat weekend)
else (DOW = Number.Mod(WeekStartDay - 1 + 7, 7) or
DOW = Number.Mod(WeekStartDay - 2 + 7, 7))
, type logical),
// Holiday flag: join to HolidayTable
WithHoliday = if HolidayTable = null
then Table.AddColumn(WithIsWknd, "IsHoliday", each false, type logical)
else
let
HolidayDates = List.Transform(
Table.Column(HolidayTable, "Date"),
each _
),
Tagged = Table.AddColumn(WithIsWknd, "IsHoliday",
each List.Contains(HolidayDates, [Date]),
type logical)
in
Tagged,
// Working day: not a weekend AND not a holiday
WithWorkDay = Table.AddColumn(WithHoliday, "IsWorkingDay",
each not [IsWeekend] and not [IsHoliday],
type logical),
// Working day index within the month (useful for payroll, SLA calculations)
// This requires sorting and ranking within each Year-Month group
Sorted = Table.Sort(WithWorkDay, {{"Date", Order.Ascending}}),
WithWDIdx = Table.AddColumn(Sorted, "WorkingDayOfMonth",
each
let
ThisDate = [Date],
ThisYrMo = Date.Year(ThisDate) * 100 + Date.Month(ThisDate),
MonthRows = Table.SelectRows(Sorted,
(r) => Date.Year(r[Date]) * 100 + Date.Month(r[Date]) = ThisYrMo
and r[IsWorkingDay] = true
and r[Date] <= ThisDate)
in
if [IsWorkingDay]
then Table.RowCount(MonthRows)
else null
, Int64.Type)
Performance warning on
WorkingDayOfMonth. TheTable.SelectRowsinsideTable.AddColumnis an O(n²) operation — for each row, it scans all rows. For a 10-year date table (~3,650 rows), this is acceptable. For a 50-year span or if you're refreshing on a slow gateway, consider computing working day indices in DAX instead, usingRANKXwith a filter. We include it here for completeness, but mark it as optional.
A more performant alternative for working day counts, if you only need "days until end of month" or "days since start of month," is to precompute the full set of working days as a list and use List.Count with a filter — still O(n) overall because the list is built once.
Date tables become dramatically more useful when they carry "is this date in the current period" flags. These allow your DAX measures to filter without explicit date parameters:
// ── 6. Relative Period Flags ─────────────────────────────────────────
Today = DateTime.Date(DateTime.LocalNow()),
WithIsToday = Table.AddColumn(WithWDIdx, "IsToday",
each [Date] = Today, type logical),
WithCurWk = Table.AddColumn(WithIsToday, "IsCurrentWeek",
each Date.WeekOfYear([Date], Day.Sunday) = Date.WeekOfYear(Today, Day.Sunday)
and Date.Year([Date]) = Date.Year(Today),
type logical),
WithCurMo = Table.AddColumn(WithCurWk, "IsCurrentMonth",
each Date.Month([Date]) = Date.Month(Today)
and Date.Year([Date]) = Date.Year(Today),
type logical),
WithCurQtr = Table.AddColumn(WithCurMo, "IsCurrentCalendarQuarter",
each Date.QuarterOfYear([Date]) = Date.QuarterOfYear(Today)
and Date.Year([Date]) = Date.Year(Today),
type logical),
WithCurFY = Table.AddColumn(WithCurQtr, "IsCurrentFiscalYear",
each
let
TodayFY = if FiscalYearStartMonth = 1
then Date.Year(Today)
else if Date.Month(Today) >= FiscalYearStartMonth
then Date.Year(Today) + 1
else Date.Year(Today)
in
[FiscalYear] = TodayFY
, type logical)
Caution with
DateTime.LocalNow(). In Power BI Service, this returns UTC time. If your organization is in UTC-5 and the model refreshes at 11 PM,IsTodaycould be a day ahead. Consider accepting aReferenceDateparameter and defaulting it toDateTime.Date(DateTime.LocalNow())— that way, you can override it in testing or in time-zone-sensitive deployments.
Now let's put all the pieces together into a single, production-ready function. Clean up column ordering and apply final type enforcement:
(
StartDate as date,
EndDate as date,
FiscalYearStartMonth as number,
WeekStartDay as number,
HolidayTable as nullable table
) as table =>
let
// ── 1. Date Spine ──────────────────────────────────────────────────
DateCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DateCount, #duration(1,0,0,0)),
BaseTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
TypedTable = Table.TransformColumnTypes(BaseTable, {{"Date", type date}}),
// ── 2. Calendar Attributes ─────────────────────────────────────────
WithYear = Table.AddColumn(TypedTable, "CalendarYear", each Date.Year([Date]), Int64.Type),
WithQtr = Table.AddColumn(WithYear, "CalendarQuarterNum", each Date.QuarterOfYear([Date]), Int64.Type),
WithQtrLbl = Table.AddColumn(WithQtr, "CalendarQuarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
WithMonth = Table.AddColumn(WithQtrLbl, "MonthNum", each Date.Month([Date]), Int64.Type),
WithMonthNm = Table.AddColumn(WithMonth, "MonthName", each Date.ToText([Date], "MMMM"), type text),
WithMonthSh = Table.AddColumn(WithMonthNm, "MonthShort", each Date.ToText([Date], "MMM"), type text),
WithYrMo = Table.AddColumn(WithMonthSh, "YearMonth", each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type),
WithDay = Table.AddColumn(WithYrMo, "DayOfMonth", each Date.Day([Date]), Int64.Type),
WithDOWNum = Table.AddColumn(WithDay, "DayOfWeekNum", each Date.DayOfWeek([Date], Day.Sunday), Int64.Type),
WithDOWNm = Table.AddColumn(WithDOWNum, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
WithDOYNum = Table.AddColumn(WithDOWNm, "DayOfYear", each Date.DayOfYear([Date]), Int64.Type),
WithDateKey = Table.AddColumn(WithDOYNum, "DateKey", each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]), Int64.Type),
// ── 3. Week Numbering ──────────────────────────────────────────────
WithAdjDOW = Table.AddColumn(WithDateKey, "DayOfWeekAdj",
each Number.Mod(Date.DayOfWeek([Date], Day.Sunday) - WeekStartDay + 7, 7), Int64.Type),
WithWkUS = Table.AddColumn(WithAdjDOW, "WeekNumUS",
each Date.WeekOfYear([Date], Day.Sunday), Int64.Type),
WithISOWk = Table.AddColumn(WithWkUS, "ISOWeekNum",
each
let d = [Date],
DayOfWkMon = Date.DayOfWeek(d, Day.Monday),
Thursday = Date.AddDays(d, 3 - DayOfWkMon),
ISOYear = Date.Year(Thursday),
Jan4 = #date(ISOYear, 1, 4),
Jan4Mon = Date.AddDays(Jan4, -Date.DayOfWeek(Jan4, Day.Monday)),
WeekNum = Duration.Days(Thursday - Jan4Mon) / 7 + 1
in Number.RoundDown(WeekNum)
, Int64.Type),
WithISOYr = Table.AddColumn(WithISOWk, "ISOWeekYear",
each Date.Year(Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday))), Int64.Type),
WithISOLbl = Table.AddColumn(WithISOYr, "ISOWeekLabel",
each Text.From([ISOWeekYear]) & "-W" & Text.PadStart(Text.From([ISOWeekNum]), 2, "0"), type text),
// ── 4. Fiscal Periods ──────────────────────────────────────────────
WithFYFixed = Table.AddColumn(WithISOLbl, "FiscalYear",
each if FiscalYearStartMonth = 1 then Date.Year([Date])
else if Date.Month([Date]) >= FiscalYearStartMonth then Date.Year([Date]) + 1
else Date.Year([Date])
, Int64.Type),
WithFQ = Table.AddColumn(WithFYFixed, "FiscalQuarterNum",
each Number.RoundUp((Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1) / 3)
, Int64.Type),
WithFQLbl = Table.AddColumn(WithFQ, "FiscalQuarter", each "FQ" & Text.From([FiscalQuarterNum]), type text),
WithFM = Table.AddColumn(WithFQLbl,"FiscalMonthNum", each Number.Mod(Date.Month([Date]) - FiscalYearStartMonth + 12, 12) + 1, Int64.Type),
WithFYQ = Table.AddColumn(WithFM, "FiscalYearQuarter",each [FiscalYear] * 10 + [FiscalQuarterNum], Int64.Type),
WithFYM = Table.AddColumn(WithFYQ, "FiscalYearMonth", each [FiscalYear] * 100 + [FiscalMonthNum], Int64.Type),
WithFYLbl = Table.AddColumn(WithFYM, "FiscalYearLabel", each "FY" & Text.From([FiscalYear]), type text),
// ── 5. Weekends, Holidays, Working Days ───────────────────────────
WithIsWknd = Table.AddColumn(WithFYLbl, "IsWeekend",
each
let DOW = Date.DayOfWeek([Date], Day.Sunday)
in DOW = Number.Mod(WeekStartDay - 1 + 7, 7) or
DOW = Number.Mod(WeekStartDay - 2 + 7, 7)
, type logical),
WithHoliday = if HolidayTable = null
then Table.AddColumn(WithIsWknd, "IsHoliday", each false, type logical)
else Table.AddColumn(WithIsWknd, "IsHoliday",
each List.Contains(Table.Column(HolidayTable, "Date"), [Date]),
type logical),
WithWorkDay = Table.AddColumn(WithHoliday, "IsWorkingDay",
each not [IsWeekend] and not [IsHoliday], type logical),
// ── 6. Relative Period Flags ───────────────────────────────────────
Today = DateTime.Date(DateTime.LocalNow()),
WithIsToday = Table.AddColumn(WithWorkDay, "IsToday", each [Date] = Today, type logical),
WithCurWk = Table.AddColumn(WithIsToday, "IsCurrentWeek", each Date.WeekOfYear([Date], Day.Sunday) = Date.WeekOfYear(Today, Day.Sunday) and Date.Year([Date]) = Date.Year(Today), type logical),
WithCurMo = Table.AddColumn(WithCurWk, "IsCurrentMonth", each Date.Month([Date]) = Date.Month(Today) and Date.Year([Date]) = Date.Year(Today), type logical),
WithCurQtr = Table.AddColumn(WithCurMo, "IsCurrentCalendarQuarter", each Date.QuarterOfYear([Date]) = Date.QuarterOfYear(Today) and Date.Year([Date]) = Date.Year(Today), type logical),
WithCurFY = Table.AddColumn(WithCurQtr, "IsCurrentFiscalYear",
each
let TodayFY = if FiscalYearStartMonth = 1 then Date.Year(Today)
else if Date.Month(Today) >= FiscalYearStartMonth then Date.Year(Today) + 1
else Date.Year(Today)
in [FiscalYear] = TodayFY
, type logical),
// ── 7. Final Cleanup and Column Order ─────────────────────────────
FinalTable = Table.SelectColumns(WithCurFY, {
"DateKey", "Date", "CalendarYear", "CalendarQuarter", "CalendarQuarterNum",
"MonthNum", "MonthName", "MonthShort", "YearMonth",
"DayOfMonth", "DayOfYear", "DayOfWeekNum", "DayOfWeekName", "DayOfWeekAdj",
"WeekNumUS", "ISOWeekNum", "ISOWeekYear", "ISOWeekLabel",
"FiscalYear", "FiscalYearLabel", "FiscalQuarterNum", "FiscalQuarter",
"FiscalMonthNum", "FiscalYearQuarter", "FiscalYearMonth",
"IsWeekend", "IsHoliday", "IsWorkingDay",
"IsToday", "IsCurrentWeek", "IsCurrentMonth",
"IsCurrentCalendarQuarter", "IsCurrentFiscalYear"
})
in
FinalTable
Save this as a query named fnDateDimension. To invoke it from another query:
let
Holidays = /* your holiday source query */,
DateDim = fnDateDimension(
#date(2020, 1, 1), // Start
#date(2026, 12, 31), // End
10, // Fiscal year starts October
1, // Weeks start Monday
Holidays // Holiday table
)
in
DateDim
The function accepts any table with a Date column. In practice, your holiday source might be a SharePoint list, a web API, or a static Excel file. Here's a simple pattern for a hardcoded list (for US federal holidays, you'd typically load from an API or maintain in SharePoint):
// Query: Holidays
let
HolidayData = {
[Date = #date(2024, 1, 1), HolidayName = "New Year's Day"],
[Date = #date(2024, 7, 4), HolidayName = "Independence Day"],
[Date = #date(2024, 11, 28),HolidayName = "Thanksgiving"],
[Date = #date(2024, 12, 25),HolidayName = "Christmas Day"],
[Date = #date(2025, 1, 1), HolidayName = "New Year's Day"],
[Date = #date(2025, 7, 4), HolidayName = "Independence Day"],
[Date = #date(2025, 11, 27),HolidayName = "Thanksgiving"],
[Date = #date(2025, 12, 25),HolidayName = "Christmas Day"]
},
HolidayTable = Table.FromRecords(HolidayData,
type table [Date = date, HolidayName = text])
in
HolidayTable
Tip: If you maintain holidays in SharePoint, connect via the SharePoint List connector, filter to your holiday list, and select just the
Datecolumn before passing it to the function. The function only needs theDatecolumn to exist — any additional columns are fine and will be ignored by theList.Containscall.
Build a complete date dimension for a retail organization with the following requirements:
Steps:
fnDateDimension function from above. Name it fnDateDimension.Holidays with at least 6 holiday dates in the format shown above.DateDim that invokes fnDateDimension with the parameters listed above, passing the Holidays query as the last argument.DateDim to the data model. Disable load for Holidays and fnDateDimension (right-click, uncheck "Enable Load").FiscalYearLabel, FiscalQuarter, MonthName, and IsWorkingDay. Verify that the fiscal months and quarters align with a February fiscal year start.IsWorkingDay and FiscalYear from your date dimension.Verification checklist:
FiscalMonthNum = 1, and January 2023 should show FiscalYear = 2023 (since the FY ends in January)IsHoliday = TRUE and IsWorkingDay = FALSEThis happens when passing a HolidayTable that has a differently named date column. If your holiday source uses HolidayDate instead of Date, either rename it before passing to the function, or parameterize the holiday date column name. The quick fix is to add a Table.RenameColumns step in your Holidays query.
The most common cause is using the "starts in" vs "ends in" convention incorrectly. Print out a few dates on either side of the fiscal year boundary and manually verify: if your fiscal year starts October 1, then September 30 should be the last day of one fiscal year and October 1 the first day of the next. Double-check that FiscalYearStartMonth = 10 is what you're passing.
ISO week 53 only exists in years where December 31 falls on Thursday (or Wednesday in leap years). If your data shows week 53, it's almost certainly correct. Cross-check against a known-good source like the ISO 8601 standard calendar or a site like timeanddate.com. The most common mistake here is confusing ISOWeekYear with CalendarYear — always pair them together.
If you're using WeekStartDay = 5 (Friday) for a Middle East work week, the weekend calculation shifts accordingly. Test by checking a specific Saturday and Sunday and verifying whether they show as weekends. The formula Number.Mod(WeekStartDay - 1 + 7, 7) computes the day just before the week starts, which is the last day of the "weekend." Trace through the math manually if results look wrong.
The most common performance bottleneck is the holiday join using List.Contains inside Table.AddColumn. For a 10-year date table with ~100 holidays, this evaluates List.Contains roughly 3,650 times, each scanning up to 100 items. This is fast in practice (milliseconds), but if you're seeing slow refresh, try extracting the holiday list outside the column-level expression:
HolidayList = if HolidayTable = null then {} else List.Buffer(Table.Column(HolidayTable, "Date")),
WithHoliday = Table.AddColumn(WithIsWknd, "IsHoliday",
each List.Contains(HolidayList, [Date]), type logical)
List.Buffer materializes the list in memory once, so subsequent List.Contains calls don't re-evaluate the holiday source query on each row.
Date.ToText([Date], "MMMM") returns month names in the locale of the Power Query session, which can cause inconsistent results when refreshing in Power BI Service (which uses a server locale, often en-US). To force English regardless of where the model refreshes, use: Date.ToText([Date], "MMMM", "en-US").
You now have a production-grade date dimension generator built entirely in M. Let's review what the function delivers:
FiscalYearStartMonth parameter, covering year, quarter, and month with both sort keys and labelsList.Buffer patternThe natural next extensions to this function are:
4-4-5 Retail Calendar: Instead of standard months, retail organizations divide 13 four-week blocks into quarterly patterns of 4, 4, and 5 weeks. This requires a separate period assignment table joined to the date spine, not a purely calculated approach — but the function you've built is a perfect base to extend.
Named Periods from an External Source: Large organizations often have custom period names ("Holiday Season," "Back to School") that live in a planning system. Add a LEFT JOIN to a periods table using Table.Join or Table.NestedJoin on the date range.
Time Zone Offset Column: For organizations operating across time zones with UTC-normalized fact tables, adding a UTCOffset column (or even expanding to time-grain rows) is a natural extension.
Parameterized Loading via Power BI Parameters: Connect StartDate, EndDate, and FiscalYearStartMonth to Power BI Desktop Parameters (Manage Parameters in the ribbon). This gives analysts a UI to reconfigure the date range without opening the Advanced Editor.
The function you've built today handles the real, messy cases that generic date table scripts ignore. That's what makes it genuinely useful in production — not the column count, but the precision of the logic underneath.
Learning Path: Advanced M Language