Date.StartOfMonth - Date Function

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

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

Syntax

Date.StartOfMonth( dateTime as any ) as any

Description

Date.StartOfMonth determines the start of the month for a dateTime parameter (of type date, datetime, or datetimezone).

Examples

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

Shifting a Date Value

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

// Output: #date( 2024, 6, 1 )
Date.StartOfMonth( #date( 2024, 6, 15 ) )

This expression takes the date June 15, 2024 and returns the first day of the month, June 1, 2024.

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

Date.StartOfMonth shifts date values to beginning of month in Power Query M

Shifting a DateTime Value

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

// Output: #datetime( 2024, 1, 1, 0, 0, 0 )
Date.StartOfMonth( #datetime( 2024, 1, 15, 18, 30, 0 ) )

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

Date.StartOfMonth moves datetime values to start of month in Power Query M

Shifting a DateTimeZone Value

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

// Output: #datetimezone( 2024, 6, 1, 0, 0, 0, 1, 0 )
Date.StartOfMonth( #datetimezone( 2024, 6, 15, 15, 0, 0, 1, 0 ) )

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

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