Date.QuarterOfYear Function in Power Query M (6 Examples)

Date.QuarterOfYear takes a date and tells you which quarter of the year it falls in, returning a number from 1 to 4. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to group orders, sales, or any dated records by quarter, this is the function you reach for.

Table of Contents

Syntax of Date.QuarterOfYear Function

Date.QuarterOfYear(dateTime as any) as nullable number

where

dateTime (required, any). The date value to read the quarter from. It can be a date, datetime, or datetimezone.

Returns: a number from 1 to 4 for the calendar quarter the date sits in. If dateTime is null, it returns null.

In plain terms, you hand it a date and it gives you the quarter that date belongs to.

Example 1: Get the quarter from a date

Read the quarter from a single date, 15 March 2024.

let
    Source = Date.QuarterOfYear(#date(2024,3,15))
in
    Source

Result: 1

March sits in the first quarter, so the function returns 1.

Example 2: See all four quarter boundaries

This grabs one date from each quarter and joins the answers, so the whole pattern is in front of you.

let
    Q1 = Date.QuarterOfYear(#date(2024,1,1)),
    Q2 = Date.QuarterOfYear(#date(2024,4,1)),
    Q3 = Date.QuarterOfYear(#date(2024,7,1)),
    Q4 = Date.QuarterOfYear(#date(2024,10,1)),
    Source = Text.Combine({Text.From(Q1),Text.From(Q2),Text.From(Q3),Text.From(Q4)},"-")
in
    Source

Result: 1-2-3-4

January starts Q1, April starts Q2, July starts Q3, and October starts Q4. Each quarter is a three-month block.

Example 3: Use it on a datetime value

The function accepts a datetime, not just a plain date. It reads the date part and ignores the time.

let
    Source = Date.QuarterOfYear(#datetime(2024,9,4,22,0,15))
in
    Source

Result: 3

September falls in the third quarter. The time part is along for the ride and changes nothing.

Example 4: Add a Quarter column to a table

Tagging each row with its quarter is what you will reach for most often.

Say you have an Example4 query with an OrderID and an OrderDate column.

You want a new column that shows the quarter for each order.

Here is the starting data:

OrderID	OrderDate
INV-501	2023-02-18
INV-502	2023-05-27
INV-503	2023-08-14
INV-504	2023-11-30

Set the OrderDate column to a date type, then add the quarter with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"OrderDate", type date}}),
    AddQuarter = Table.AddColumn(Typed,"Quarter",each Date.QuarterOfYear([OrderDate]),Int64.Type)
in
    AddQuarter

Each row now carries the quarter its order date falls in.

The result keeps every row and adds the Quarter column:

OrderID	OrderDate	Quarter
INV-501	2023-02-18	1
INV-502	2023-05-27	2
INV-503	2023-08-14	3
INV-504	2023-11-30	4

February lands in Q1, May in Q2, August in Q3, and November in Q4.

Example 5: Build a “Q1-2023” label

A bare quarter number loses the year, so Q1 from 2023 looks the same as Q1 from 2024. Pair Date.QuarterOfYear with Date.Year and you get a label like Q1-2023 that reads on its own.

Start with the same kind of order table:

OrderID	OrderDate
INV-601	2023-03-31
INV-602	2023-12-31

Build the label, then drop the original date column:

let
    Source = Excel.CurrentWorkbook(){[Name="Example5"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"OrderDate", type date}}),
    AddQuarterLabel = Table.AddColumn(Typed,"QuarterLabel",each "Q" & Text.From(Date.QuarterOfYear([OrderDate])) & "-" & Text.From(Date.Year([OrderDate]))),
    Removed = Table.RemoveColumns(AddQuarterLabel,{"OrderDate"})
in
    Removed

The & operator stitches the Q, the quarter number, the dash, and the year into one text value. To format the whole date as text in one step instead, use Date.ToText.

The result is a clean quarter label per order:

OrderID	QuarterLabel
INV-601	Q1-2023
INV-602	Q4-2023

31 March falls in Q1 and 31 December in Q4, both in 2023, so the labels read Q1-2023 and Q4-2023.

Example 6: Pass a null date

If the date can be missing, it helps to know what comes back.

let
    Source = Date.QuarterOfYear(null)
in
    Source

Result: null

A null date gives back null rather than an error, so a blank cell flows straight through.

Things to keep in mind with Date.QuarterOfYear

It returns the calendar quarter, not a fiscal one. January through March is always 1, April through June 2, and so on. There is no setting to shift the year start.
For a fiscal quarter, shift the date first. A fiscal year starting in a different month needs an offset, for example Date.QuarterOfYear(Date.AddMonths([OrderDate],-3)) for a year that begins in April.
A text date throws an error. A value like "2023-02-18" gives Expression.Error: We cannot convert a value of type Text to type Date. Set the column to type date first, as Examples 4 and 5 do, or convert it with Date.FromText.

Common questions about Date.QuarterOfYear

What is the difference between Date.QuarterOfYear and Date.Month?

Date.QuarterOfYear returns a quarter from 1 to 4, while Date.Month returns the month number from 1 to 12. Use the quarter function when you want three-month buckets instead of single months.

Can I get the start or end date of the quarter?

Not from this function, since it only returns the quarter number. Use Date.StartOfQuarter and Date.EndOfQuarter to get the actual boundary dates.

List of All Power Query Functions

Related Power Query Functions / Articles: