Skip to content

QueryAndChart.

Power BI month-to-date sales in DAX: TOTALMTD that resets every month

9-minute read

“How is this month going?” needs a figure that starts again on the first of every month — and a comparison with the same stretch of the same month last year, so a strong month is not just a long one. This guide builds that comparison with four measures: a total, a month-to-date total with TOTALMTD, the same total shifted one year back, and the change between them. The headline result: month-to-date sales for October 2025 of $13,436.42 against $10,994.74 for October 2024, up 22.21%.

Key result: Sales month to date for October 2025: $13,436.42, against $10,994.74 for October 2024 — MTD YoY +22.21%. The reset is the point: February 2025 month to date is $9,571.47, not the $21,114.04 a running total would show.

Download the data and the measures: the sales file (CSV) (66 rows) and the measures file (DAX) (the four measures below, ready to paste). The data is a constructed sample — three product lines, monthly revenue from January 2024 to October 2025 — not a real company's sales.

Grouped bar chart of month-to-date sales by month, January to October 2025 in amber against the same months of 2024 in grey. The 2025 bar is taller in every month, reaching $13,436 in October against $10,995 a year earlier.
Month-to-date sales by month, 2025 vs 2024.

What you need

1. Import the sales file

In Power BI Desktop, go to Home > Get data > Text/CSV, pick the sales file, and choose Load. Check the column types after loading: Month must be a date, Revenue a decimal number, and Units a whole number. If Month loads as text, the relationship in step 3 will not work — change the type in Power Query first.

2. Build the calendar table

Time-intelligence functions need a date table: a table with one row per day, continuous, with no gaps. Microsoft's documentation says to mark one of the tables containing a date column as the date table before using time-intelligence functions (Microsoft Learn: Set and use date tables in Power BI Desktop). Create it as a calculated table:

Modeling > New table

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "MonthYear", FORMAT ( [Date], "mmm yyyy" )
)

CALENDAR on its own returns a table with a single Date column. ADDCOLUMNS adds the columns the visuals need: Year and MonthNumber as numbers, MonthName as the full month name, Quarter as Q1 to Q4, and MonthYear as a label such as Jan 2025. Marking the table as the date table removes the built-in date hierarchies Desktop would otherwise generate. Build one by hand instead: in the Fields pane, right-click Year and choose Add to hierarchy > New hierarchy. Then drag Quarter and MonthName in below Year, in that order. Then sort the months in calendar order: select MonthName and choose Sort by column > MonthNumber on the Column tools ribbon. The full treatment is in Build a Power BI date table in DAX.

Then mark it: select the Calendar table in the Fields pane, right-click, and choose Mark as date table > Mark as date table, with Date as the date column. The same Learn page describes both the right-click path and the Table tools ribbon path. The range must cover every month in the sales file — January 2024 through October 2025 — or the prior-year lookup in step 4 returns blank for the uncovered months.

3. Relate the two tables

In Model view, drag Calendar[Date] onto Sales[Month]. The relationship is one-to-many, single direction, from Calendar to Sales. One calendar date matches many sales rows; no sales row matches more than one calendar date. If the relationship shows as many-to-many or inactive, the Month column is the wrong type — go back to step 1 and fix it.

4. Write the four measures

Create each measure on the Sales table (Modeling > New measure). The measures file (DAX) holds the same four measures, ready to paste.

Total Sales = SUM ( Sales[Revenue] )

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

Sales MTD PY =
CALCULATE (
    [Sales MTD],
    SAMEPERIODLASTYEAR ( Calendar[Date] )
)

MTD YoY % Change =
DIVIDE (
    [Sales MTD] - [Sales MTD PY],
    [Sales MTD PY]
)

How it works: TOTALMTD evaluates the value of the expression for the month to date, in the current context — from the first of the month through the latest date in the current filter (Microsoft Learn, TOTALMTD function (DAX)). Sales MTD PY reevaluates that month-to-date measure under dates moved one year back. SAMEPERIODLASTYEAR returns the current context shifted one year back, which Microsoft documents as equivalent to DATEADD(dates, -1, year) (Microsoft Learn, SAMEPERIODLASTYEAR function (DAX)). CALCULATE evaluates the measure under that shifted context (Microsoft Learn, CALCULATE function (DAX)). DIVIDE returns blank instead of an error when the prior-year total is zero or blank (Microsoft Learn, DIVIDE function (DAX)).

One honest note about this sample: it holds one sales row per category per month, so at month level Sales MTD lands on the month total — January reads $11,542.57 and October reads $13,436.42. The measure's value here is the reset. February 2025 month to date is $9,571.47, not the $21,114.04 a running total or a year-to-date measure would show, because the accumulation starts again on 1 February. On daily data the same measure climbs day by day within the month and drops back on the first of the next. For the measure that resets only on 1 January, see the year-to-date guide.

5. Put them in a visual

Add a clustered column chart. Put the Calendar hierarchy (Year > Quarter > MonthName) on the x-axis and drill to the month level. Use [Sales MTD] and [Sales MTD PY] as the values, and add a card visual showing [MTD YoY % Change] formatted as a percentage with two decimals. Filter the chart to 2025 if you want the current-year view only; the prior-year bars still compute, because the shift happens inside the measure, not in the visual filter. A year slicer, if you add one, uses Calendar[Year].

6. Check the numbers

Put the Calendar hierarchy (Year > Quarter > MonthName), drilled to the month level, [Total Sales], [Sales MTD], [Sales MTD PY], and [MTD YoY % Change] in a table visual. Every row must match this table:

MonthTotal Sales ($)Sales MTD ($)Sales MTD PY ($)MTD YoY %
January 202511,542.5711,542.578,851.91+30.40%
February 20259,571.479,571.477,367.88+29.91%
March 202512,194.2512,194.259,055.10+34.67%
April 202512,329.8312,329.839,299.99+32.58%
May 202512,556.4212,556.429,904.12+26.78%
June 202512,196.7012,196.7010,107.84+20.67%
July 202512,184.2212,184.2210,457.11+16.52%
August 202513,279.9913,279.9910,285.04+29.12%
September 202513,415.3013,415.3010,132.66+32.40%
October 202513,436.4213,436.4210,994.74+22.21%

October 2025 month to date breaks down by category as Desks $6,233.12, Chairs $4,590.14, and Lamps $2,613.16 — put Category on the rows of a second table to see it. If a month shows a blank Sales MTD PY, the calendar table is the suspect: either it was not marked as the date table, or its range does not cover the prior year. If Sales MTD keeps climbing past a month boundary instead of dropping back, the date argument is not Calendar[Date] — check that the measure points at the calendar table, not the sales table's own Month column.

Traps

TRAP — SHIFTING THE MONTH, NOT THE MONTH TO DATE. Writing CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Calendar[Date] ) ) gives the same dates a year ago under a plain total, not the prior month to date under the month-to-date measure. Shift the month-to-date measure itself instead: CALCULATE ( [Sales MTD], SAMEPERIODLASTYEAR ( Calendar[Date] ) ). On monthly data the two forms agree; on daily data they part company mid-month, when the plain total covers the whole prior month and the shifted measure covers only the same days.

TRAP — NO DATE TABLE, NO MONTH TO DATE. TOTALMTD against the sales table's own Month column returns blank, not an error, because the function needs a continuous date column to define the month. Build and mark the calendar table first, as in step 2.

TRAP — READING A MONTH TOTAL AS A DAILY CLIMB. On data with one row per month, month to date and the month total are the same number, so the chart looks flat against the monthly bars. That is the data's grain, not a broken measure. The climb appears when the sales table holds daily rows: the measure then rises through the month and resets on the first.

TRAP — DIVIDE BY A BLANK PRIOR YEAR. Writing ([Sales MTD] - [Sales MTD PY]) / [Sales MTD PY] with the division operator errors on months with no prior-year sales. DIVIDE returns blank instead. The first year of any new product line is exactly that case.

End state

A calendar table marked as the date table, a one-to-many relationship to sales, and four measures. The table visual shows ten months of 2025, each month standing on its own: month to date runs from $11,542.57 in January to $13,436.42 in October, resetting every month instead of accumulating, and the month-to-date change against last year stays between +16.52% and +34.67%, ending at +22.21% in October. The same four measures work on any sales table with a date column and a calendar table — only the table and column names change.

Sources

Written November 12, 2026