Date.DayOfYear - Date Function

Date.DayOfYear is a Power Query M function that returns a number representing the day of the year in the provided date, datetime, or datetimezone value. The function returns the day of the year as an integer.

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

Syntax

Date.DayOfYear( dateTime as any ) as nullable number

Description

Date.DayOfYear takes a date, datetime, or datetimezone value (referred to as dateTime) and returns a number ranging from 1 to 366. This number represents the position of dateTime in the year, indicating the day of the year.

Examples

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

Calculating Day of the Year for a Date Value

When you apply the Date.DayOfYear function to a date value, it returns the day of the year for that date. For example, to find the day of the year for June 15, 2024:

// Output: 167
Date.DayOfYear( #date( 2024, 6, 15 ) )

This expression returns 167, as June 15th is the 167th day of the year in 2024.

To visualize this, imagine a table with a date column containing multiple date values. You can find the day of the year for each date by using Date.DayOfYear.

Date.DayOfYear returns the day number in a year of a respective date in Power Query

Calculating Day of the Year for a DateTime Value

The function also supports datetime values. To find the day of the year for a datetime value of June 15th, 2024, 12:00 AM:

// Output: 167
Date.DayOfYear( #datetime( 2024, 6, 15, 0, 0, 0 ) )

This expression returns 167, as June 15th is the 167th day of the year in 2024.

Calculating Day of the Year for a DateTimeZone Value

Similarly, applying the function to a datetimezone value returns the day of the year for that datetimezone:

// Output: 196
Date.DayOfYear( #datetimezone( 2024, 7, 15, 0, 0, 0, 1, 0 ) )

This expression returns 196, as July 15th is the 196th day of the year in 2024.

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

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