Date.DaysInMonth Function in Power Query M (5 Examples)

If you want to find how many days a given month has in Power Query, the Date.DaysInMonth function gives you that count from any date value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Date.DaysInMonth Function

Date.DaysInMonth(dateTime as any) as nullable number

where

dateTime (required, any). A date, datetime, or datetimezone value. The function looks at the month of this value and counts the days in it.

Returns: a number, the count of days in that month (28 to 31). If dateTime is null, it returns null.

In plain terms, you hand it a date and it tells you how many days are in that date’s month.

Example 1: Count the days in a given month

Check how many days are in December 2011.

let
    Source = Date.DaysInMonth(#date(2011, 12, 1))
in
    Source

Result: 31

The day part of the date does not matter. Any date in December returns 31.

Example 2: February in a leap year

2024 is a leap year, so February has an extra day.

let
    Source = Date.DaysInMonth(#date(2024, 2, 1))
in
    Source

Result: 29

The function knows 2024 is a leap year and returns 29 without any extra work from you.

Example 3: February in a normal year

Compare that to 2023, which is not a leap year.

let
    Source = Date.DaysInMonth(#date(2023, 2, 1))
in
    Source

Result: 28

Same month, but a different year flips the count. You never have to spell out the leap-year rule.

Example 4: Pass a datetimezone value

The input does not have to be a plain date. A datetimezone works too.

let
    Source = Date.DaysInMonth(#datetimezone(2024, 9, 15, 8, 30, 0, 5, 30))
in
    Source

Result: 30

The time and zone parts are ignored. The function only reads the month, September, and returns 30.

Example 5: Add a days-in-month column to a table

Say you track SaaS plans and the year and month each billing cycle falls in. You want a column showing how many days that cycle has.

Here is the starting data:

Plan	Year	Month
Starter	2024	2
Growth	2024	4
Scale	2025	7
Legacy	2023	2

Build the date from the Year and Month columns, then add the count:

let
    Source = Excel.CurrentWorkbook(){[Name="BillingPeriods"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}, {"Month", Int64.Type}}),
    AddDays = Table.AddColumn(Typed, "DaysInMonth", each Date.DaysInMonth(#date([Year], [Month], 1)), Int64.Type)
in
    AddDays

The new column holds the day count for each row’s month and year:

Plan	Year	Month	DaysInMonth
Starter	2024	2	29
Growth	2024	4	30
Scale	2025	7	31
Legacy	2023	2	28

Both February rows land correctly, 29 for leap-year 2024 and 28 for 2023.

Things to keep in mind with Date.DaysInMonth

You pass a date, not a year and month. There is no Date.DaysInMonth(year, month) form. To check a specific month, build a date for it with #date(year,month,1) as in Example 5.
The day you pass is irrelevant. #date(2024,2,1) and #date(2024,2,28) both return 29. Only the year and month are read.
It counts calendar days, not a duration. The output is a month length, not a Duration.Days value pulled from the gap between two dates. They answer different questions.
The result is a plain number you can keep computing with. Feed it straight into more date math, for example #date([Year],[Month],Date.DaysInMonth(#date([Year],[Month],1))) to get the last day of a month.
null in gives null out. A blank date column returns null, not an error. Guard it if a downstream step expects a number.

Common questions about Date.DaysInMonth

How is this different from Date.EndOfMonth?

Date.DaysInMonth returns a number like 30. Date.EndOfMonth returns the actual last date of the month, such as 30/09/2024. Use the first when you need the count, the second when you need the date itself. If you then want to shift that date, reach for Date.AddDays.

List of All Power Query Functions

Related Power Query Functions / Articles: