Skip to content

QueryAndChart.

Power BI quarter-to-date sales in DAX: TOTALQTD from the quarter start

9-minute read

Quarters are how most teams report: a quarter-to-date figure climbs for three months, then starts again at the next quarter. This guide builds that figure with four measures: a total, a quarter-to-date total with TOTALQTD, the same total shifted one year back, and the change between them. The headline result: quarter-to-date sales for the third quarter of 2025, read at September, of $38,879.51 against $30,874.81 for the third quarter of 2024, up 25.93%. October then starts a new quarter at $13,436.42.

Key result: Sales quarter to date for Q3 2025, at September: $38,879.51, against $30,874.81 for Q3 2024 — QTD YoY +25.93%. October 2025 quarter to date is just $13,436.42, because the fourth quarter starts over.

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.

Line chart of quarter-to-date sales by month, January to October 2025 in amber against the same months of 2024 in grey. Both lines climb for three months then drop back at April, July, and October; the 2025 line peaks at $38,880 in September.
Quarter-to-date sales by month, 2025 vs 2024 — the drop at each quarter start is the reset.

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 QTD =
TOTALQTD (
    [Total Sales],
    Calendar[Date]
)

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

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

How it works: TOTALQTD evaluates the value of the expression for the dates in the quarter to date, in the current context — from the first day of the quarter through the latest date in the current filter (Microsoft Learn, TOTALQTD function (DAX)). Sales QTD PY reevaluates that quarter-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)).

The reset dates are the quarter starts: 1 January, 1 April, 1 July, and 1 October. Watch the check table in step 6: April and July drop back to the single month — $12,329.83 and $12,184.22 — because a new quarter has begun, and October does the same at $13,436.42. That sawtooth is the signature of the measure. It sits between its two neighbours: TOTALYTD resets only on 1 January, so its line climbs all year, and TOTALMTD resets on the first of every month, so at month level it never accumulates at all. Pick the reset that matches the question being asked.

5. Put them in a visual

Add a line chart. Put the Calendar hierarchy (Year > Quarter > MonthName) on the x-axis and drill to the month level. Use [Sales QTD] and [Sales QTD PY] as the values, and add a card visual showing [QTD 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 line still computes, because the shift happens inside the measure, not in the visual filter. A year slicer, if you add one, uses Calendar[Year]. The line should fall at April, July, and October — a line that climbs straight through a quarter start is showing a different measure.

6. Check the numbers

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

MonthTotal Sales ($)Sales QTD ($)Sales QTD PY ($)QTD YoY %
January 202511,542.5711,542.578,851.91+30.40%
February 20259,571.4721,114.0416,219.79+30.17%
March 202512,194.2533,308.2925,274.89+31.78%
April 202512,329.8312,329.839,299.99+32.58%
May 202512,556.4224,886.2519,204.11+29.59%
June 202512,196.7037,082.9529,311.95+26.51%
July 202512,184.2212,184.2210,457.11+16.52%
August 202513,279.9925,464.2120,742.15+22.77%
September 202513,415.3038,879.5130,874.81+25.93%
October 202513,436.4213,436.4210,994.74+22.21%

If a month shows a blank Sales QTD 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 QTD equals Total Sales on every row, the measure is not accumulating within the quarter — check that the date argument is Calendar[Date], not the sales table's own Month column. If it never drops back at a quarter start, the visual is showing a year-to-date or running-total measure instead.

Traps

TRAP — SHIFTING THE MONTH, NOT THE QUARTER TO DATE. Writing CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Calendar[Date] ) ) gives the single month a year ago, not the prior quarter to date. Shift the quarter-to-date measure itself instead: CALCULATE ( [Sales QTD], SAMEPERIODLASTYEAR ( Calendar[Date] ) ). The check table catches this at once — February's prior-year figure would read $7,367.88 instead of $16,219.79.

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

TRAP — READING THE SAWTOOTH AS A SALES DROP. October quarter to date ($13,436.42) sits far below September ($38,879.51), and that looks like a collapse. Nothing fell: October is the first month of a new quarter, so the measure counts October alone. Compare October with October 2024 quarter to date — also a single month, $10,994.74 — and the quarter is ahead by 22.21%.

TRAP — DIVIDE BY A BLANK PRIOR YEAR. Writing ([Sales QTD] - [Sales QTD PY]) / [Sales QTD 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 in four sawtooth climbs: quarter to date peaks at $33,308.29 in March, $37,082.95 in June, and $38,879.51 in September, dropping back to the single month at each quarter start, and the quarter-to-date change against last year stays between +16.52% and +32.58% across the year. 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