Date.DaysInMonth - Date Function

Date.DaysInMonth is a Power Query M function that returns the number of days in the month in the date, datetime, or datetimezone value. The function returns the number of days in the month as an integer.

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

Syntax

Date.DaysInMonth( dateTime as any ) as nullable number

Description

Date.DaysInMonth calculates the number of days in the month of the given date, datetime, or datetimezone value (dateTime). It returns a number between 28 and 31, reflecting the month’s length in days.

Examples

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

Calculating Days in a Month for a Date Value

When you apply the Date.DaysInMonth function to a date value, it returns the number of days in the month of that date. For example, to find the number of days in June 2024:

// Output: 30
Date.DaysInMonth( #date( 2024, 6, 15 ) )

This expression returns 30 days, as June has 30 days.

To visualize this, imagine a table with a date column containing multiple date values. You can find the number of days in the month for each date by using Date.DaysInMonth.

Date.DaysInMonth returns the Number of Days in a Month in Power Query

Calculating Days in a Month for a DateTime Value

The function also supports datetime values. To find the number of days in the month for a datetime value of June 15th, 2024, 12:00 AM:

// Output: 30
Date.DaysInMonth( #datetime( 2024, 6, 15, 0, 0, 0 ) )

This expression returns 30 days, as June has 30 days.

Calculating Days in a Month for a DateTimeZone Value

Similarly, applying the function to a datetimezone value returns the number of days in the month:

// Output: 31
Date.DaysInMonth( #datetimezone( 2024, 7, 15, 0, 0, 0, 1, 0 ) )

This expression returns 31 days, as July has 31 days.

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

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