Date.AddYears - Date Function

Date.AddYears is a function in the Power Query M language that adds a specified number of years 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.AddYears(
   dateTime as any,
   numberOfYears as number,
) as any

Description

The Date.AddYears function allows you to add a specific number of years to a given dateTime argument. This argument can be of type date, datetime, or datetimezone. The function includes a numberOfYears argument, which specifies how many years you want to add (or subtract) to the given dateTime value.

Examples

Adding Years to a Date

Let’s start with a simple example where we add 5 years to a date. Suppose we have January 1, 2024, and we want to add 5 years to this date. Here’s how to do it:

// Output: #date( 2029, 1, 1 )
Date.AddYears( #date( 2024, 1, 1), 5 )

In this example, the Date.AddYears function takes January 1, 2024, and adds 5 years to it. The result is January 1, 2029.

Subtracting Years from a Date

The Date.AddYears function can also subtract years from a date. For instance, let’s subtract 3 years from January 1, 2024:

// Output: #date( 2021, 1, 1 )
Date.AddYears( #date( 2024, 1, 1), -3 )

Here, the function moves the date back by 3 years, resulting in January 1, 2021.

Adding Years to a DateTime Value

The Date.AddYears function also works with DateTime values. Let’s add 2 years to a specific date and time:

// Output: 1/1/2026, 10:00:00 AM
Date.AddYears( #datetime( 2024, 1, 1, 10, 0, 0 ), 2 )

In this case, the function takes a DateTime value of January 1, 2024, at 10:00 AM and adds 2 years to it, resulting in January 1, 2026, at the same time.

Subtracting Years from a DateTime Value

You can also subtract years from a DateTime value. For example, let’s subtract 1 year from January 1, 2024, at 3:00 PM:

// Output: 1/1/2023, 3:00:00 PM
Date.AddYears( #datetime( 2024, 1, 1, 15, 0, 0 ), -1 )

Here, the function moves the DateTime value back by 1 year, resulting in January 1, 2023, at 3:00 PM.

Adding Years to a DateTimeZone Value

The Date.AddYears function is also capable of working with DateTimeZone values. Let’s add 4 years to a DateTimeZone value:

// Output: 1/1/2028, 12:00:00 PM +02:00
Date.AddYears( #datetimezone( 2024, 1, 1, 12, 0, 0, 2, 0 ), 4 )

In this example, the function takes a DateTimeZone value of January 1, 2024, at 12:00 PM with a +02:00 time zone offset, and adds 4 years to it. The result is January 1, 2028, at 12:00 PM with the same time zone offset.

Other functions related to Date.AddYears are:

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