DATESMTD
Returns a table of dates from the first day of the month up to the last date in the filter context.
DATESMTD(<dates>)Returns: A table of dates, used as a filter inside CALCULATE.
What it does
DATESMTD returns the dates from the first day of the current month to the last date in the context. CALCULATE uses them as a filter, so any measure can be shown month to date. It is the most flexible of the month-to-date tools, because other filters and date shifts can be combined with it.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
Example
A payroll team wants to see payroll paid so far this month, for a mid-month cost review. The payments are in a table related to the Calendar.
Payroll Paid MTD =
CALCULATE(
SUM('Payroll'[NetPay]),
DATESMTD('Calendar'[Date])
)
In a view for October 15, 2026, the filter covers October 1 to October 15. The figure resets on the first of each month.
Example: a second, different use
Expenses month to date for one cost center, compared with the same days of the prior month. The date filter and the cost center filter work together.
Expenses MTD Prior Month =
CALCULATE(
SUM('Expenses'[Amount]),
DATEADD(DATESMTD('Calendar'[Date]), -1, MONTH),
'Expenses'[CostCenter] = "Sales"
)
If the current view runs from October 1 to October 15, the prior-month figure covers September 1 to September 15 for the Sales cost center.
Things to watch
- DATESMTD returns a table. It works inside CALCULATE or as the table of an iterator, and it cannot be used as a number.
- A partial month should be compared with the same days in the prior month, not with the full month.
- The date column must be the Calendar column, and the Calendar must have no gaps.
- If the report date is later than the last posted day, the figure may be understated. Check the last posting date before you publish a flash.
In business terms
DATESMTD supports month-to-date expense control, payroll cost tracking, cash position reports during the close and the mid-month flash. It gives the controller an early view of the month and a fair comparison with the prior month.