Date.EndOfQuarter
Returns the last day of the calendar quarter that contains a date.
Date.EndOfQuarter(dateTime as any) as dateReturns: The last day of the quarter as a date, or null when the input is null.
What it does
Date.EndOfQuarter returns the last day of the quarter, which is March 31, June 30, September 30 or December 31 for a calendar quarter. Quarter end is the date that many tax deposits, covenant tests and board reports use, so this is the value to use when a report says "as of quarter end".
Syntax and arguments
- dateTime: a date or a datetime value.
Example
A covenant test is run at each quarter end. Every transaction is tagged with the quarter end date it falls under, so the test uses the right balance date.
let
Transactions = #table(type table [Txn = text, TxnDate = date, Amount = number],
{{"T-1", #date(2026, 2, 14), 400}, {"T-2", #date(2026, 10, 29), 900}}),
TestDate = Table.AddColumn(Transactions, "QuarterEnd", each Date.EndOfQuarter([TxnDate]), type date)
in
TestDate
The February transaction belongs to the quarter ending March 31, 2026. The October transaction belongs to the quarter ending December 31, 2026.
Example: a second, different use
Compare a balance at each quarter end by grouping the balance rows on the quarter end date.
Things to watch
- It returns the calendar quarter end. A fiscal quarter end needs a fiscal calendar.
- The result is a date with no time part.
- A quarter end can fall on a weekend, so decide whether the test is on that day or the last business day before it.
- Null in gives null out.
In business terms
Quarter end tests, covenant calculations, sales tax returns and board reporting are all dated to quarter end. Tagging every row with the quarter end date means a late entry is still counted in the right quarter.