Date.QuarterOfYear - Date Function

Date.QuarterOfYear is a Power Query M function that returns a number from 1 to 4 indicating which quarter of the year the date falls in. You can provide a date, datetime, or datetimezone value.

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

Syntax

Date.QuarterOfYear( dateTime as any ) as nullable number

Description

The Date.QuarterOfYear function returns the quarter number (1 through 4) for a given date, datetime, or datetimezone value.

Examples

Let’s explore how to use this function with various examples.

Retrieving the Quarter Number from a Date Value

To get the quarter number for a specific date, use the following expression:

// Output: 4
Date.QuarterOfYear( #date( 2024, 12, 28 ) )

In this example, the function returns 4 for the date December 28, 2024, indicating that the date falls in the fourth quarter of the year.

A common application of the Date.QuarterOfYear function is within a calendar table. For instance, you might have a range of date values representing the end of each month in 2024. By applying the function to these dates, it returns the corresponding quarter number, which can be useful for organizing and analyzing data by quarters.

In the image below, the Date.QuarterOfYear function is applied to a range of end-of-month dates for 2024. The function returns each quarter number three times, corresponding to the dates within each quarter.

Date.QuarterOfYear-returns-the-quarter-of-year-number-from-a-date-value-in-Power-Query-M

Applying Date.QuarterOfYear to Different Value Types

Despite its name suggesting use with date values, the Date.QuarterOfYear function is also applicable to datetime and datetimezone values.

Example with a DateTime Value

You can extract the quarter number from a datetime value as follows:

Date.QuarterOfYear( #datetime( 2024, 5, 25, 5, 0, 15 ) ) // Returns 2

In this case, the function returns 2 for the datetime value May 25, 2024, 05:00:15, indicating it is in the second quarter of the year.

Example with a DateTimeZone Value

Similarly, you can apply the function to a datetimezone value:

Date.QuarterOfYear( #datetimezone( 2024, 9, 4, 22, 0, 15, 4, 0 ) ) // Returns 3

This example shows the function returning 3 for the datetimezone value September 4, 2024, 22:00:15, with a timezone offset of +04:00, indicating the third quarter.

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

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