Build a proper date table for business reporting

A date table represents the reporting calendar independently of transactions. It provides one row per date and attributes such as year, month and fiscal period, so reports can group activity consistently and represent dates with no observed transactions.

The calendar must match the business question. A complete calendar does not prove that transaction data is complete, and a missing fact row should not automatically be interpreted as zero activity.

Inspect the supplied calendar

DimDate.csv in the Power BI retail lab covers January 1, 2025 through December 31, 2026: 730 unique, nonblank daily dates. It includes Year, MonthNumber, MonthName, YearMonth and MonthStart.

The fixture has sales only in January and February 2026. The extra dates allow the model to represent full calendar years; they do not supply a valid prior-year sales comparison. Label missing historical coverage before calculating growth against 2025.

Import Date and MonthStart as date types. Confirm uniqueness, minimum, maximum and consecutive-day coverage. Microsoft's date-table design guidance describes the requirements and available construction approaches.

Relate the correct business date

Create an active one-to-many relationship from DimDate[Date] to FactSales[OrderDate], with single-direction filtering from date to sales. Both fields should represent compatible date values.

A transaction timestamp containing a non-midnight time does not directly equal a date-only calendar key. Derive a reporting date under an explicit timezone rule before creating the relationship. Do not simply discard time before deciding which local calendar date the event belongs to.

The lab already supplies normalized OrderDate values, so no timezone conversion is needed for its synthetic example.

Use year-aware period labels

MonthName alone combines January across all years. Use YearMonth or a year-plus-month hierarchy when the report spans several years. Sort MonthName by MonthNumber when using names within an appropriate year context.

For a longer horizon, a numeric YearMonthKey such as year × 100 + month can provide chronological sorting for a display label. Keep labels and sort keys distinct so a renamed label does not change the underlying period identity.

Validate that every label has an unambiguous corresponding sort value in the intended column. A fiscal label can require a fiscal-period key rather than a calendar-month number.

Choose the time-intelligence approach deliberately

Power BI's date-table and time-intelligence capabilities evolve. Follow the current guidance for the approach you use, including date-table marking where required, rather than assuming that every newer calendar feature behaves like an older classic time-intelligence function.

Record the model version and configuration with the exercise. The date-table configuration documentation is the source for current application steps; the fixture's calendar checks are independent of those UI settings.

Represent business calendars explicitly

If the report asks for trading-day comparisons, add business-day and holiday attributes for the relevant operating calendar. A weekday flag alone cannot represent regional holidays, planned closures or store-specific trading schedules.

For a 4-4-5 fiscal calendar or another nonstandard period structure, obtain an authoritative calendar table and validate period boundaries. Do not approximate fiscal months by ordinary calendar months merely because the resulting chart looks familiar.

Keep the owner and version of the business calendar visible. A corrected holiday or fiscal-period definition can change historical comparisons without any transaction changing.

Verify the relationship through controls

Place YearMonth from DimDate beside the Paid Net Paise measure. January 2026 should show 47,500 and February 22,000. The total is 69,500. An unexpected blank group or unchanged total under month selection suggests a relationship, type or filter issue to investigate.

Check an exact month boundary: the February 1 purchase belongs to February, not January. For delivery-date reporting, use an explicitly named alternate relationship or role-playing date table rather than silently reinterpreting OrderDate.

The lab verifies calendar structure and expected sales totals in Python/SQLite. DAX evaluation and Power BI relationship behaviour remain application-review steps.

Exercise: add a fiscal-year-start attribute for an April-to-March reporting year. Verify March 31 and April 1 land in the intended fiscal years, and document the naming convention so “FY2026” cannot mean two different periods.

NeuraPath's Data Analytics with Generative AI course connects calendar modelling with reliable business reporting. A useful date table makes period definitions and coverage limits explicit.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Single versus bidirectional filtering in Power BI.
• Continue with Power Query merge versus append with reconciled totals.