Downloads
Power Query MDateBeginner

Date.EndOfMonth

Returns the last day of the month that contains a date.

Syntax
Date.EndOfMonth(dateTime as any) as date

Returns: The last day of the month as a date, or null when the input is null.

What it does

Date.EndOfMonth returns the last calendar day of the month that holds a date. It handles February in a leap year and the 30 day months for you. It is the standard way to set a balance date, a period end or a due date that is "end of month", and it is more reliable than adding a month to a date and subtracting a day.

Syntax and arguments

Example

A loan schedule shows interest due at the end of each month. The due date for each row is the last day of that row's month, whatever the month length.

let
    Schedule = #table(type table [Period = text, AnyDayInMonth = date],
        {{"February 2026", #date(2026, 2, 10)}, {"October 2026", #date(2026, 10, 1)}, {"February 2028", #date(2028, 2, 10)}}),
    DueDate = Table.AddColumn(Schedule, "InterestDue", each Date.EndOfMonth([AnyDayInMonth]), type date)
in
    DueDate

February 2026 is not a leap year, so its due date is February 28, 2026. October 2026 gives October 31. February 2028 is a leap year, so its due date is February 29, 2028.

Example: a second, different use

Set the balance date of a month end journal to the period end, so every entry for the month carries the same date.

Things to watch

In business terms

Month end is when most entries are booked, balances are measured and reports are dated. Using the real last day of each month, leap years included, keeps the balance date consistent across the whole close.

Related functions

All Power Query M functions · View as Markdown · M basics