Date.AddMonths - Date Function

Date.AddMonths is a function in the Power Query M language that adds a specified number of months to a date or datetime value. The resulting value is returned as a date, datetime, or datetimezone.

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

Syntax

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

Description

Date.AddMonths adds a specified number of months (numberOfMonths) to a date, datetime, or datetimezone value (dateTime). It returns the resulting date after the addition.

Examples

Adding Months to a Date

Let’s start with a straightforward example. We want to add 3 months to the date of June 1, 2024. Here’s how we do it:

Date.AddMonths( #date( 2024, 6, 1), 3 ) // Output: #date( 2024, 9, 1 )

In this example, the Date.AddMonths function takes a date value as input and adds 3 months to it. The result is September 1, 2024.

Subtracting Months from a Date

The Date.AddMonths function can also subtract months from a date. Let’s see an example where we subtract 3 months from June 1, 2024:

Date.AddMonths( #date( 2024, 6, 1), -3 ) // Output: #date( 2024, 3, 1 )

Here, the function moves the date back to March 1, 2024, by subtracting 3 months.

Adding Months to a DateTime Value

The Date.AddMonths function is flexible and can also work with DateTime values. Let’s add 3 months to a specific date and time:

// Output: 9/1/2024, 12:00:00 PM
Date.AddMonths( #datetime( 2024, 6, 1, 12, 0, 0 ), 3 )

In this case, the input and output are both DateTime values. The function adds 3 months to June 1, 2024, at 12:00 PM, resulting in September 1, 2024, at the same time.

Adding Months to a DateTimeZone Value

You can even use the Date.AddMonths function with DateTimeZone values. Here’s how you can add 3 months to a DateTimeZone value:

// Output: 9/1/2024, 12:00:00 PM +02:00
Date.AddMonths( #datetimezone( 2024, 6, 1, 12, 0, 0, 2, 0 ), 3 )

In this example, the function takes a DateTimeZone value as input and adds 3 months to it. The result is September 1, 2024, at 12:00 PM, with the same time zone offset of +02:00.

Learn more about Date.AddMonths in the following articles:

Date Table with Monthly Increments in Power Query
Are you looking to create a date table with Monthly increments in Power Query? With Power Query, creating a monthly calendar is a breeze. » Read more

Other functions related to Date.AddMonths are:

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