Date.EndOfQuarter - Date Function

Date.EndOfQuarter is a Power Query M function that returns the end of the quarter, preserving time zone information. The function returns the end of the quarter as a date, datetime, or datetimezone value.

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

Syntax

Date.EndOfQuarter( dateTime as any ) as any

Description

Date.EndOfQuarter calculates the end of the quarter for a dateTime parameter (of type date, datetime, or datetimezone), preserving time zone information.

Examples

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

Shifting a Date Value

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

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

This expression returns the last day of the quarter, 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 quarters by using Date.EndOfQuarter.

Date.EndOfQuarter to shift date values in Power Query M

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 quarter, you can use:

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

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

Date.EndOfQuarter to shift datetime values in Power Query M

Shifting a DateTimeZone Value

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

// Output: #datetimezone( 2024, 9, 30, 23, 59, 59.9999999, 1, 0 )
Date.EndOfQuarter( #datetimezone( 2024, 8, 15, 0, 0, 0, 1, 0 ) )

Learn more about Date.EndOfQuarter 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.EndOfQuarter are:

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