Power BI Exercise - Creating a Date Table and Month-to-Date Sales (Retail Dataset)

Build a reliable calendar model and track cumulative monthly sales.

In this guided exercise, you will create a proper Date table, connect it to the retail Sales table, and calculate month-to-date sales. You will also build a daily sales trend and validate the cumulative results in DAX Query View.

Learning goal: build the date dimension required for reliable calendar analysis and understand how a date filter reaches the Sales table through a relationship.

Date tableRelationshipTOTALMTDTime intelligence

Why create a Date table?

The Sales[SaleDate] column records when each transaction occurred. It is suitable for storing transaction dates, but a dedicated Date table provides one continuous row for every calendar date and reusable fields such as Year, Month, and Day.

A good Date table should have:

• One row for every date in the selected period
• A unique Date column with no blank values
• No missing dates
• A relationship to the transaction table
• Useful calendar attributes for report grouping and filtering

Calculated table versus measure: the Date table and its calendar columns are stored model structures. Measures such as Sales MTD are calculations evaluated according to the current report filters.

Before you begin

Open the retail Power BI file and confirm that:

• The transaction table is named Sales.
• Sales[SaleDate] has the Date data type.
• The measure [Total Sales] exists.
• The unfiltered Total Sales value is 21,788.

Step 1: Create the Date table

1. Select the Table tools or Modeling ribbon.
2. Select New table.
3. Enter the following DAX expression and press Enter.

Date =
VAR FirstYear = YEAR ( MIN ( Sales[SaleDate] ) )
VAR LastYear = YEAR ( MAX ( Sales[SaleDate] ) )
RETURN
    ADDCOLUMNS (
        CALENDAR (
            DATE ( FirstYear, 1, 1 ),
            DATE ( LastYear, 12, 31 )
        ),
        "Year", YEAR ( [Date] ),
        "Month Number", MONTH ( [Date] ),
        "Month Name", FORMAT ( [Date], "MMMM" ),
        "Year Month", FORMAT ( [Date], "YYYY-MM" ),
        "Day", DAY ( [Date] ),
        "Day Name", FORMAT ( [Date], "dddd" )
    )

For the current dataset, this creates one row for every day from 1 January 2026 through 31 December 2026.

Why use full years? The retail dataset currently contains only January transactions, but a full-year Date table is ready for later months and follows established date-table design guidance.

Step 2: Mark it as a Date table

1. Select the new Date table.
2. On the Table tools ribbon, select Mark as date table.
3. Select the Date[Date] column.
4. Select OK.

This identifies the unique, continuous date column Power BI should use for classic time-intelligence calculations.

Step 3: Sort Month Name correctly

1. Select Date[Month Name].
2. On the Column tools ribbon, select Sort by column.
3. Select Date[Month Number].

This prevents month names from appearing alphabetically.

Step 4: Create the relationship

1. Open Model view.
2. Drag Date[Date] onto Sales[SaleDate].
3. Confirm the cardinality is One to many (1:*).
4. Confirm the cross-filter direction is Single.
5. Confirm the relationship is active, and select OK.

Relationship side	Column	Role
One side	Date[Date]	One unique row per date
Many side	Sales[SaleDate]	Multiple sales can occur on a date

If the relationship cannot be created: check that both columns use the Date data type. If SaleDate includes a time component, convert it to a date-only value in Power Query first.

Step 5: Create the Month-to-Date measure

1. Select the Sales table.
2. Select New measure.
3. Enter the following formula.

Sales MTD =
TOTALMTD (
    [Total Sales],
    'Date'[Date]
)

TOTALMTD evaluates Total Sales from the start of the current month through the current date in the filter context.

Step 6: Build a daily sales table

1. Open Report view.
2. Add a Table visual.
3. Add Date[Date], [Total Sales], and [Sales MTD].
4. In the visual filter pane, set [Total Sales] to is not blank.
5. Sort the table by Date in ascending order.

Date	Daily Total Sales	Sales MTD
3 Jan 2026	2,599	2,599
4 Jan 2026	267	2,866
6 Jan 2026	1,598	4,464
8 Jan 2026	718	5,182
10 Jan 2026	796	5,978
12 Jan 2026	2,099	8,077
14 Jan 2026	999	9,076
17 Jan 2026	3,598	12,674
20 Jan 2026	695	13,369
22 Jan 2026	1,467	14,836
24 Jan 2026	916	15,752
26 Jan 2026	1,499	17,251
28 Jan 2026	2,398	19,649
29 Jan 2026	1,587	21,236
31 Jan 2026	552	21,788

Step 7: Validate the result in DAX Query View

1. Open DAX Query View.
2. Enter the following query.
3. Select Run.

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Date'[Date],
        "Daily Sales", [Total Sales],
        "Sales MTD", [Sales MTD]
    ),
    NOT ISBLANK ( [Daily Sales] )
)
ORDER BY 'Date'[Date]

The query should return 15 transaction dates. The final row, 31 January 2026, should show Sales MTD of 21,788.

Step 8: Create the sales trend chart

1. Add a Line chart to the report canvas.
2. Place Date[Date] on the X-axis.
3. Place [Total Sales] and [Sales MTD] on the Y-axis.
4. Set the X-axis type to Continuous.
5. Give the chart the title Daily Sales and Month-to-Date Sales.

Expected pattern: Daily Sales rises and falls by transaction date. Sales MTD increases progressively and reaches 21,788 on 31 January.

Step 9: Test the Date table

1. Add a slicer using Date[Date].
2. Change the slicer style to Between.
3. Select 1 January through 17 January 2026.
4. Confirm Total Sales is 12,674.
5. Extend the end date to 31 January and confirm Total Sales returns to 21,788.

The slicer filters the Date table. The active relationship then propagates the matching dates to the Sales table.

Knowledge check

1. Why should a Date table contain dates with no sales transactions?
2. Why is Date placed on the one side of the relationship?
3. What is the difference between daily Total Sales and Sales MTD?
4. Why should Month Name be sorted by Month Number?
5. How does the Date slicer filter the Sales table?

Troubleshooting

Problem	What to check
Sales MTD equals daily sales on every row.	Verify the Date table is connected to Sales and that the visual uses Date[Date], not Sales[SaleDate].
Sales MTD is blank.	Confirm the relationship is active and both relationship columns use the Date data type.
The chart shows every day of 2026.	Apply a visual filter for Total Sales is not blank, or restrict the displayed date range.
Months appear alphabetically.	Sort Month Name by Month Number.
The final total is not 21,788.	Clear other slicers and verify that all 15 retail records are loaded.

Exercise complete

You have created a reusable Date dimension, connected it to the Sales fact table, calculated month-to-date sales, and built a time-based visual. The model is now ready for additional calculations such as previous-month sales, year-to-date sales, and year-over-year growth when more months of data become available.

Completion checklist

• The Date table covers the complete 2026 calendar year.
• The Date table is marked using Date[Date].
• A one-to-many active relationship connects Date to Sales.
• Sales MTD reaches 21,788 on 31 January.
• The table, query, slicer, and line chart produce the expected results.

References

• Microsoft Learn: Design guidance for date tables in Power BI Desktop
• Microsoft Learn: CALENDAR function (DAX)
• Microsoft Learn: TOTALMTD function (DAX)