Building a shared calendar for business reporting

Build a shared calendar by agreeing on what each reporting day and period means, then maintain one set of definitions that reports can reuse. Set the date range, timezone, week boundary, financial-period convention and the event date used by each measure. A date table cannot resolve an ambiguous business rule by itself.

Define the reporting day

Start with the source timestamp and the decision the report supports. A sale recorded near midnight may fall on different dates in UTC and in a specified local timezone.

Define the conversion before deriving its reporting date. Record how a missing timestamp is handled and whether the latest day is complete at the reporting cut-off.

An order may have placed, paid, shipped and completed dates. “Orders this month” must identify one of them. Label each date role in the model and in measures that use it.

Specify the calendar columns

A reusable date row has a unique date and the approved labels people repeatedly need: calendar year, month, quarter, week and any financial year or period. Include a sortable period value as well as a display label, so months appear in date order and identically named months in different years remain distinct.

Decide which year a week crossing a year boundary belongs to.

If an organisation reports from July through June, decide whether “FY2026” means the year ending in June 2026 or another convention. Use the chosen label consistently.

Do not infer an organisation's financial calendar from its Australian location. If teams use different approved calendars, name and document each one.

A complete daily calendar can show dates with no transactions. That helps reports display empty periods, but an absent transaction should not silently become zero when a source load is incomplete.

For Power BI classic time intelligence, Microsoft's date-table guidance calls for a marked date table with unique, non-blank, continuous dates spanning full years. Its newer calendar-based time intelligence generally does not require marking the table, subject to specified exceptions. Check which approach the model actually uses.

Reuse the definition without changing fact grain

A warehouse date dimension can supply period labels to several models. Teams building local Power BI date tables can use a maintained template or shared source so definitions stay aligned. Power BI's automatic date/time feature creates hidden tables for date columns; it does not provide one date table that filters several fact tables.

A monthly budget remains a monthly fact even when reports use a daily calendar. Joining the full budget to every day and summing it would repeat its value. Compare it with actuals aggregated to the same month, or apply an approved allocation rule if a daily view is needed. A calendar aligns periods; it does not create missing detail.

Check boundaries before reuse

Check the first and last day of a month, a financial-year boundary, a week crossing a year, a day with no events and an event near the timezone cut-off against the reporting owner's definitions.

For a measure with more than one date role, compare a fixed period under each role and explain the difference. Record the calendar's owner, range, timezone, version and change process so affected reports can be identified when a rule changes.

Pre-Reuse Validation Checklist for Shared Calendars