ENDOFMONTH DAX Function (Time Intelligence) Context Transition
Returns the end of month.
Syntax
Parameter | Attributes | Description |
---|---|---|
Dates |
The name of a column containing dates or a one column table containing dates. |
Return values
A table containing a single column and single row with a date value.
Remarks
The <Dates> argument can be any of the following:
- A reference to a date/time column. Only in this case a context transition applies because the column reference is replaced by
- CALCULATETABLE ( DISTINCT ( <Dates> ) )
- A table expression that returns a single column of date/time values.
- A Boolean expression that defines a single-column table of date/time values (when used in a CALCULATE filter argument).
ENDOFMONTH filters <Dates> into a 1-row, 1-column table that shows only the latest date, in the entire <Dates> column devoid of all filters, that is in the same year and month as the latest visible date in <Dates>.
The following ENDOFMONTH call
ENDOFMONTH ( 'Date'[date] )
is a more efficient implementation of the following semantically equivalent expression:
VAR LastDateVisible = CALCULATE ( MAX ( 'Date'[Date] ) ) VAR LastYearVisible = YEAR ( LastDateVisible ) VAR LastMonthVisible = MONTH ( LastDateVisible ) VAR DaysInMonth = FILTER ( ALL ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = LastYearVisible && MONTH ( 'Date'[Date] ) = LastMonthVisible ) VAR LastDayInMonth = MAXX ( DaysInMonth, 'Date'[Date] ) VAR Result = CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Date] = LastDayInMonth ) RETURN Result
Related functions
Other related functions are:
Last update: Dec 7, 2019 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Jes Hansen
MSDN documentation: https://docs.microsoft.com/en-us/dax/endofmonth-function-dax