Date.StartOfYear - Date Function

Date.StartOfYear is a Power Query M function that returns the start of the year 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.StartOfYear( dateTime as any ) as any

Description

Date.StartOfYear finds the start of the year for a given dateTime parameter (of type date, datetime, or datetimezone).

Examples

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

Shifting a Date Value

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

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

This expression returns the first day of the year, January 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 years by using Date.StartOfYear.

Date.StartOfYear shifts date to the beginning of the year in Power Query M

Shifting a DateTime Value

The function also supports datetime values. To shift a datetime value of May 15th, 2024, 12:00 PM to the start of the year, you can use:

// Output: #datetime( 2024, 1, 1, 0, 0, 0 )
Date.StartOfYear( #datetime( 2024, 5, 15, 12, 0, 0 ) )

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

Date.StartOfYear shifts date and time values to start of the year in Power Query M

Shifting a DateTimeZone Value

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

// Output: #datetimezone( 2024, 1, 1, 0, 0, 0, 1, 0 )
Date.StartOfYear( #datetimezone( 2024, 5, 15, 12, 0, 0, 1, 0 ) )

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

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