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

If you want to shift a date forward or backward by a set number of days in Power Query, the Date.AddDays function is what you reach for.

Give it a date and a count of days, 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.AddDays Function

Date.AddDays(dateTime as any, numberOfDays 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.
numberOfDays (required, number). How many days 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 numberOfDays. It rolls across month and year boundaries and respects leap years.

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

Example 1: Add days to a date

Take March 12, 2025 and move it forward by 9 days.

let
    Source = Date.AddDays(#date(2025,3,12),9)
in
    Source

Result: 2025-03-21

The function lands on March 21, 2025, nine days after the start date.

Example 2: Subtract days with a negative number

A negative numberOfDays walks the date backward. Start from July 4, 2025 and go back 21 days.

let
    Source = Date.AddDays(#date(2025,7,4),-21)
in
    Source

Result: 2025-06-13

The result crosses back into June and lands on June 13, 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: Cross a month boundary in a leap year

Date.AddDays handles the calendar math for you, including leap years. Start from February 20, 2024 and add 14 days.

let
    Source = Date.AddDays(#date(2024,2,20),14)
in
    Source

Result: 2024-03-05

Because 2024 is a leap year, February has 29 days, so the count rolls into March and lands on March 5, 2024.

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

Most of the time you will use this to add an offset to a whole column. Say you have an Invoices table and want a due date for each invoice, based on its own payment terms. You could also pull in today’s date in Power Query as the starting point if the offset is from the current day.

Here is the starting data:

InvoiceID	InvoiceDate	PaymentDays
INV-1001	2025-01-15	30
INV-1002	2025-02-25	45
INV-1003	2025-11-20	30

Type the InvoiceDate column as date first, then add a DueDate column with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"InvoiceDate", type date},{"PaymentDays", Int64.Type}}),
    AddDueDate = Table.AddColumn(Typed,"DueDate",each Date.AddDays([InvoiceDate],[PaymentDays]),type date),
    Result = Table.SelectColumns(AddDueDate,{"InvoiceID","DueDate"})
in
    Result

Each row adds its own PaymentDays count to its InvoiceDate.

The result keeps the ID and the calculated due date:

InvoiceID	DueDate
INV-1001	2025-02-14
INV-1002	2025-04-11
INV-1003	2025-12-20

The Table.TransformColumnTypes step matters here. If InvoiceDate is still a text column, Date.AddDays throws a type error.

Things to keep in mind with Date.AddDays

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.
numberOfDays must be a number. Passing text like "30" fails. Convert it with Number.FromText if it arrives as text.
A decimal only adds a partial day on a datetime. With a plain date, a fractional numberOfDays is rounded down to whole days. On a datetime, the fractional part shifts the time portion (0.5 adds 12 hours).

Common questions about Date.AddDays

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

Both shift a date by days. You could write someDate + #duration(7,0,0,0) to add a week, and it gives the same answer.

Date.AddDays(someDate,7) says exactly what it does, so use it when the offset is a whole number of days.

List of All Power Query Functions

Related Power Query Functions / Articles: