Date.EndOfMonth - Date Function

Date.EndOfMonth is a Power Query M function that returns the end of the month. The function returns the end of the month as a date, datetime, or datetimezone value.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Date.EndOfMonth( dateTime as any ) as any

Description

Date.EndOfMonth returns the last day of the month for the provided date, datetime, or datetimezone value (dateTime).

Examples

The Date.EndOfMonth function works with date, datetime and datetimezone values.

Shifting a Date Value

When you apply the Date.EndOfMonth function to a date value, it shifts the date to the end of the month:

// Output: #date( 2024, 6, 30 )
Date.EndOfMonth( #date( 2024, 6, 15 ) )

This expression returns the last day of the month, June 30, 2024.

To visualize this, imagine a table with a date column containing multiple date values. You can shift each of these to the end of their respective months by using Date.EndOfMonth.

Date.EndOfMonth to shift dates in Power Query

Shifting a DateTime Value

The function also supports datetime values. To shift a datetime value of January 15th, 2024, 12:00 AM to the end of the month, you can use:

// Output: #datetime( 2024, 1, 31, 23, 59, 59.9999999 )
Date.EndOfMonth( #datetime( 2024, 1, 15, 0, 0, 0 ) )

Not only does this shift the date to the end of the month, but it also moves the time from 12:00 AM to 11:59 PM. Here are some example datetime values to illustrate this:

Date.EndOfMonth to shift datetime values in Power Query

Shifting a DateTimeZone Value

Similarly, applying the logic to a datetimezone value shifts the date to the end of the month and the time to the end of the day, while keeping the timezone information intact:

// Output: #datetimezone( 2024, 6, 30, 23, 59, 59.9999999, 1, 0 )
Date.EndOfMonth( #datetimezone( 2024, 6, 15, 0, 0, 0, 1, 0 ) )

Learn more about Date.EndOfMonth in the following articles:

Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more

Other functions related to Date.EndOfMonth are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-endofmonth