Date.AddMonths Function in Power Query M (4 Easy Examples)

If you want to shift a date forward or backward by a set number of months in Power Query, the Date.AddMonths function is what you reach for. Give it a date and a count of months, and it hands back the new date. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Date.AddMonths Function

Date.AddMonths(dateTime as any, numberOfMonths as number) as any

where

dateTime (required, any). The starting value. It can be a date, datetime, or datetimezone, and the function returns the same kind you pass in.
numberOfMonths (required, number). How many months to add. Use a positive number to move forward and a negative number to move backward.

Returns: a value of the same type you passed in (a date in, a date out), shifted by numberOfMonths whole months. If the target month is shorter than the start day, it lands on the last day of that month.

In plain terms, you hand it a date and a number, and it gives you the date that many months later (or earlier).

Example 1: Add months to a date

Take March 12, 2025 and move it forward by 5 months.

let
    Source = Date.AddMonths(#date(2025,3,12),5)
in
    Source

Result: 2025-08-12

The function lands on August 12, 2025, five months after the start date.

Example 2: Subtract months with a negative number

A negative numberOfMonths walks the date backward. Start from August 15, 2025 and go back 6 months.

let
    Source = Date.AddMonths(#date(2025,8,15),-6)
in
    Source

Result: 2025-02-15

The result crosses back into February and lands on February 15, 2025. You do not need a separate subtract function, and if you want the gap between two dates instead, see how to calculate date difference in Power Query.

Example 3: Month-end clamping

When the target month has fewer days than the start day, the result snaps to the last day of that month. Start from January 31, 2025 and add 1 month.

let
    Source = Date.AddMonths(#date(2025,1,31),1)
in
    Source

Result: 2025-02-28

February 2025 has only 28 days, so January 31 has nowhere to land on the 31st and clamps to February 28. In a leap year it would land on the 29th.

Example 4: Add a renewal-date column to a table

Most of the time you will use this to add an offset to a whole column. Say you have a Subscriptions table and want a renewal date for each account, based on its own term length. You could also pull in today’s date in Power Query as the starting point if the term runs from the current day.

Here is the starting data:

AccountID	StartDate	TermMonths
ACC-2041	2024-06-15	12
ACC-2042	2024-11-30	3
ACC-2043	2025-01-31	1

Type the StartDate column as date first, then add a RenewalDate column with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Subscriptions"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"StartDate", type date},{"TermMonths", Int64.Type}}),
    AddRenewal = Table.AddColumn(Typed,"RenewalDate",each Date.AddMonths([StartDate],[TermMonths]),type date),
    Result = Table.SelectColumns(AddRenewal,{"AccountID","RenewalDate"})
in
    Result

Each row adds its own TermMonths count to its StartDate.

The result keeps the ID and the calculated renewal date:

AccountID	RenewalDate
ACC-2041	2025-06-15
ACC-2042	2025-02-28
ACC-2043	2025-02-28

The last two rows both clamp to February 28, 2025, because November 30 and January 31 have no matching day in that short month.

Things to keep in mind with Date.AddMonths

Month-end days clamp to the shorter target month. Adding to January 31 lands on the last day of the target month, not a rolled-over date in the next one (Example 3). Worth remembering for any end-of-month start date.
The input must be a date value, not text. A text column throws Expression.Error: We cannot convert a value of type Text to type Date. Set the column type to date first (Example 4) or wrap the value in Date.FromText.
numberOfMonths is a whole-month step. Unlike Date.AddDays, a fraction does not add part of a month. Pass a whole number.
A datetime keeps its time portion. Pass a datetime and you get a datetime back, shifted by whole months with the same time of day.

Common questions about Date.AddMonths

What is the difference between Date.AddMonths and adding a #duration?

A #duration is measured in days, hours, minutes, and seconds, so it cannot express “one month” cleanly because months vary in length.

Date.AddMonths(someDate,1) steps by a calendar month every time, which is what you want for renewals and contract terms.

List of All Power Query Functions

Related Power Query Functions / Articles: