The ENDOFMONTH function returns the last day of the month in the current context (or the last month, if the current context includes more than one) for the specified date column.
ENDOFMONTH(
dates
)
- dates: Column containing dates.
The ENDOFMONTH function returns a table with a single column and a single row with a date.
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 beginning and end of the month, the quarter and the year are shown in the following table for some dates of May 2011:
If we take the number of the month to the row headings in a matrix, the year to the column headings, and the following measure:
Last day of the month = ENDOFMONTH(DimDate[Datekey])
...to the Values field, we get the following result:
It can be seen how the result of the measure coincides with the last day of each month.
If, in the same scenario as in the previous example, we take the quarter to the row headers, the result obtained is the following:
We see how, in this case, the created measure returns the last day of the last month of the context.
Finally, if we take the week number to the row header, the result is the following:
In this case, the measure returns the last day of the month involved in the context. That is, even if the week in question includes, for example, January 5 to 12, the ENDOFMONTH function will return January 31 for that context.