Date.StartOfQuarter - Date Function

Date.StartOfQuarter is a Power Query M function that returns the start of the quarter that contains the specified date(Time). Your input must be a date, datetime, or datetimezone value.

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

Syntax

Date.StartOfQuarter( dateTime as any ) as any

Description

Date.StartOfQuarter locates the beginning of the quarter for a dateTime parameter ( of type date, datetime, or datetimezone).

Examples

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

Shifting a Date Value

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

// Output: #date( 2024, 4, 1 )
Date.StartOfQuarter( #date( 2024, 5, 15 ) )

This expression returns the first day of the quarter, April 1, 2024.

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

Date.StartOfQuarter moves date value to quarter start in Power Query

Shifting a DateTime Value

The function also supports datetime values. To shift a datetime value of January 15th, 2024, 2:30 AM to the start of the quarter, you can use:

// Output: #datetime( 2024, 1, 1, 0, 0, 0 )
Date.StartOfQuarter( #datetime( 2024, 1, 15, 2, 30, 0 ) )

Not only does this shift the date to the start of the quarter, but it also moves the time to 12:00 AM. Here are some example datetime values to illustrate this:

Date.StartOfQuarter moves date and time value to beginning of quarter in Power Query M

Shifting a DateTimeZone Value

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

// Output: #datetimezone( 2024, 7, 1, 0, 0, 0, 1, 0 )
Date.StartOfQuarter( #datetimezone( 2024, 8, 15, 2, 30, 0, 1, 0 ) )

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

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