The DATESMTD function returns a table with a single column that contains the dates from the beginning of the month in the current context to the last date in the current context.
DATESMTD(
dates
)
- dates: Column containing dates.
The DATESMTD function returns a table.
The dates argument can be a reference to a column containing dates, an expression that returns a table with a single column containing dates, or a Boolean expression that defines a table with a single column containing dates.
The current month always starts on day 1 (not customizable).
This function considers the last day of the current context and returns a table containing all the dates from the first day of the month to which that day belongs to this last day. This means that if in the current context more than one month is involved, only the last one is considered.
Depending on the context in which it is used, the result may not be as expected. In such a case, we must make sure that we have marked the calendar table as "date table" (by selecting the table in the Fields column and executing the Table Tools > Calendars > Mark as date table command):
The following sales table is considered in this example:
The Ventas measure is defined:
Ventas = SUM(Ventas[Importe])
...now we create the Ventas MTD measure that calculates accumulated sales since the beginning of the month:
Ventas MTD = CALCULATE(
Ventas[Ventas],
DATESMTD(Calendario[Fecha])
)
If we take these measures to a matrix, we obtain the following result:
It can be seen how the measure is reset to zero at the beginning of each month.
