DATE
Returns a date built from a year, a month number and a day number.
DATE(<year>, <month>, <day>)Returns: A date (a datetime value with no time part).
What it does
DATE builds a real date from three numbers. Finance models use it for fixed reporting dates such as a fiscal year end, a quarter cut-off or the date of a month-end pack. It also turns separate year, month and day columns from an older export into one date. Writing a date as DATE(2026, 6, 30) is safer than writing it as text, because text dates such as 6/30/2026 can be read differently on different computers.
Syntax and arguments
- year: a four-digit year, such as 2026. Required.
- month: the month number from 1 to 12. Required.
- day: the day of the month, from 1 to 31. Required.
Example
A company has a June 30 year end. The controller wants a measure that shows the fiscal year end date for whatever month is in the report.
FY End Date =
VAR CurrentYear = YEAR(MAX('Calendar'[Date]))
VAR FYEnd = DATE(CurrentYear, 6, 30)
RETURN
IF(MAX('Calendar'[Date]) > FYEnd, DATE(CurrentYear + 1, 6, 30), FYEnd)
For a date in October 2026, the measure returns June 30, 2027, the end of the fiscal year that started on July 1, 2026. For a date in March 2026, it returns June 30, 2026. DATE builds the two candidate dates and the IF picks the right one.
Example: a second, different use
A bank export splits the posting date into three numeric columns. In a calculated column on the ledger table, DATE joins them into one date that can be related to the Calendar table.
Posting Date =
DATE(
'GL_Entries'[PostYear],
'GL_Entries'[PostMonth],
'GL_Entries'[PostDay]
)
Things to watch
- DATE rolls a month number above 12 into the next year. DATE(2026, 13, 1) returns January 1, 2027.
- Do not pass a text date such as "6/30/2026" to DATE. Split the text into parts first, or use DATEVALUE with care.
- DATE returns a single value. To make a column of dates, use a calculated column or a Calendar table.
- Dates used with time intelligence must have no time part, or they will not match the Calendar's date column.
In business terms
Finance teams use DATE to set fixed period boundaries: fiscal year start and end, quarter cut-offs, and the reporting date for a close pack. Getting these boundary dates right is the first check in any month-end, budget or audit date review.