Date.EndOfMonth returns the last day of the month that contains a given date. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
If you want the month-end date for any date you have, whether for a billing cutoff, an accounting close, or just the 28th versus the 31st, this is the function to use.
Syntax of Date.EndOfMonth Function
Date.EndOfMonth(dateTime as any) as any
where
dateTime(required, any). Adate,datetime, ordatetimezonevalue to find the end of the month for.
Returns: the last day of the month that contains dateTime, as the same kind of value you passed in. A date in gives a date out, a datetime in gives a datetime out.
In plain terms, you give it any date in a month and it hands back the final calendar day of that month.
Example 1: Get the last day of the month for a date
Find the month-end date for 12 March 2025.
Date.EndOfMonth(#date(2025,3,12))
Result: 2025-03-31
March has 31 days, so the function returns the 31st. The day part of the input does not matter, only the month and year.
Example 2: February in a leap year
2024 is a leap year, so February has 29 days. Check what the function returns for a February date.
Date.EndOfMonth(#date(2024,2,10))
Result: 2024-02-29
The function works out that 2024 is a leap year and returns the 29th, not the 28th.
Example 3: February in a non-leap year
Now do the same for 2025, which is not a leap year.
Date.EndOfMonth(#date(2025,2,10))
Result: 2025-02-28
February 2025 has only 28 days, so the result is the 28th. You never have to keep track of which years are leap years yourself.
Example 4: Add a billing cutoff column to a table
A common job is turning each customer’s signup date into the last day of that month, so you know when their first billing period ends. From there you can calculate the date difference to find days remaining.
Say you have a Subscriptions query with a Customer and a SignupDate column.
Here is the starting data:
| Customer | SignupDate |
|---|---|
| Acme Co | 8 Jan 2025 |
| Brightline | 22 Apr 2025 |
| Coastal LLC | 11 Feb 2025 |
| Delta Group | 30 Nov 2025 |
Type the SignupDate column as a date, then add a BillingCutoff column with Date.EndOfMonth:
let
Source = Excel.CurrentWorkbook(){[Name="Subscriptions"]}[Content],
Typed = Table.TransformColumnTypes(Source,{{"SignupDate", type date}}),
Result = Table.AddColumn(Typed,"BillingCutoff",each Date.EndOfMonth([SignupDate]),type date)
in
Result
The new column holds each row’s month-end date:
| Customer | SignupDate | BillingCutoff |
|---|---|---|
| Acme Co | 8 Jan 2025 | 31 Jan 2025 |
| Brightline | 22 Apr 2025 | 30 Apr 2025 |
| Coastal LLC | 11 Feb 2025 | 28 Feb 2025 |
| Delta Group | 30 Nov 2025 | 30 Nov 2025 |
Notice the last row. Delta Group signed up on the 30th, which is already the last day of November, so the cutoff stays put.
Things to keep in mind with Date.EndOfMonth
- The input must be a real date value, not text. Passing a string like
"2025-03-12"throws a type error. Columns fromExcel.CurrentWorkbookarrive as typeany, so type the column first withTable.TransformColumnTypesbefore calling the function (see Example 4). - For a
datetime, the time is pushed to the end of the day. The result keeps the date kind but sets the time to23:59:59.9999999, not the original time. Wrap the call inDate.Fromfor a clean date, or useDate.ToTextto format it as a string. - It pairs with the other period functions.
Date.StartOfMonthgives the first day of the month,Date.EndOfYearthe last day of the year, andDate.EndOfQuarterthe last day of the quarter.
Common questions about Date.EndOfMonth
How do I get the last day of the month in Power Query?
Use Date.EndOfMonth and pass it any date in that month. It returns the final calendar day, leap years included.
What is the difference between Date.EndOfMonth and Date.DaysInMonth?
Date.EndOfMonth returns a date, the actual last day such as 2025-02-28. Date.DaysInMonth returns just the number of days as an integer, such as 28.
What is the difference between Date.EndOfMonth and Date.StartOfMonth?
Date.EndOfMonth returns the last day of the month, while Date.StartOfMonth returns the first. For a datetime, end-of-month sets the time to 23:59:59.9999999 and start-of-month sets it to 00:00:00.
List of All Power Query Functions
Related Power Query Functions / Articles:
