Date Table

Date table

Adds a fully-formed date dimension to a Power BI Project (PBIP) semantic model, plus the companion measure that turns the current date selection into a readable report title (Selected Period: Jan - Mar 2024).

The assets are TMDL, so this works on a PBIP folder on disk with no Power BI Desktop, Tabular Editor, or network access required.

Attribution

The two substantial pieces of code here are community work, redistributed with credit. Keep the attribution headers in the TMDL when you copy these assets anywhere.

Neither is published under an explicit licence; both were shared freely by their authors. The column renames, added columns, and removed ISO columns in the bundled version are local modifications -- see references/column-reference.md.

What gets added

Optional helper expressions (--include-helpers): Year_Variable (current year + 2) and TodayDate.

Workflow

1. Find the model

Ask for the PBIP path if it is not obvious from context. The script accepts the .SemanticModel folder, its definition folder, the .pbip file, or the project root.

Before writing anything, check Power BI Desktop does not have the project open -- run pbir desktop list. If it does, tell the user to close it first; Desktop holds file locks and will overwrite disk changes from its in-memory copy.

2. Confirm the calendar parameters

Do not guess these. Ask with AskUserQuestion unless the user already said:

• Date range. Default --start 2021-01-01. For the end, either a fixed --end yyyy-mm-dd (defaults to 31 Dec of the current year + 2) or --dynamic-end, which ends at 31 Dec of Year_Variable so the table extends itself every year without an edit. --dynamic-end adds the helper expressions automatically. The range must cover every date in every fact table, with no gaps.
• Fiscal year start month. Default 7 (July -- Australian/NZ convention). Use 1 for a calendar fiscal year, 4 for UK, 10 for US federal.
• Week start. --week-start 1 (default) numbers Monday as 1; 0 numbers Sunday as 0.

3. Run the script

python scripts/add_date_table.py --model "<path to project>" --dry-run
python scripts/add_date_table.py --model "<path to project>" --fy-start-month 7 --dynamic-end

Useful flags:

The script is idempotent -- re-running it reports what is already present and leaves it alone. Every lineageTag in the injected TMDL is regenerated, so the same assets can go into any number of models without collisions.

4. Wire it up

The script does not create relationships -- it cannot know your fact tables. After it runs:

1. Add relationships from each fact table's date column to DimDate[Date], single direction, many-to-one. Edit definition/relationships.tmdl directly (see the pbip:tmdl skill) or let the user do it in Desktop.
2. Consider --mark-as-date-table. It is off by default because it is only correct once [Date] is genuinely unique and contiguous, and because it disables Power BI's auto date/time hierarchies for that column. Turn it on if the user wants DAX time intelligence (DATESYTD, DATEADD) to work against this table.
3. Hide DimDate[Date] from report view only if a friendlier column is exposed -- report authors usually still need it for the axis.

5. Verify

python ../../../pbip/skills/pbip/scripts/validate_pbip.py "<path to .pbip>"

Then have the user open the project in Power BI Desktop and refresh DimDate. The M function runs entirely in memory -- there is no data source to authorize, so the refresh should succeed with no credential prompt.

If --dynamic-end was used, confirm Year_Variable refreshes before DimDate; the PBI_QueryOrder annotation the script writes puts it in the right order.

The Dates Selected measure

Returns a string describing whatever the date slicers currently select:

Drop it in a card visual at the top of a page. Two strings near the top of the DAX are meant to be edited: __PeriodPrefix (default "Selected Period: ") and __DiverseDates (default "a range of dates in ").

It is coupled to the bundled DimDate. It reads [Date], [Year], [Start Of Month], [Days in Year] and [Days In Month]. If you point it at a different date table, add those columns first or rewrite the references.

Gotchas

• IsHoliday is the string "Unknown", not a boolean, unless a holiday list was passed via --holidays. IsBusinessDay still works (it tests <> true), but do not write DAX assuming IsHoliday is boolean.
• The ISO columns are gone. The bundled function removes ISO Year, ISO Weeknumber and friends in its last step; the Week & Year / WeeknYear columns carry the ISO logic instead. Do not reintroduce a newer upstream copy of the function without re-applying the local modifications -- Dates Selected will break.
• FQuarternYear and FPeriodnYear are double, not int64. Harmless as sort keys, but they show a general number format if you drag them onto a visual.
• Month Initial and Weekday Initial contain zero-width spaces (U+200B) as padding so the twelve month initials stay distinct values. String comparisons against them will not match a plain "J".
• Do not hand-edit the M function's indentation. It lives inside a TMDL triple-backtick block where indentation is significant; a reflowed line breaks Desktop's file open. See the pbip:tmdl skill, references/authoring-gotchas.md.

Reference

• references/column-reference.md -- all 58 columns, grouped, with the local modifications vs. upstream listed at the end.
• assets/ -- the raw TMDL fragments, if you want to paste rather than run the script.