How to Add Days to a Date in Power BI

Adding a few days to a date might sound simple, but it's a fundamental task you'll face constantly when building reports. Whether you’re calculating A/R aging reports, estimating shipping deadlines outside of your ERP, or setting project timelines, knowing how to manipulate dates directly inside your dashboards is a game-changer. This tutorial will walk you through the various ways to add days to a date in Power BI, using clear, step-by-step examples.

Why Would You Need to Add Days to a Date?

Working with dates is a core part of business analytics. While your source data (from Shopify, Salesforce, etc.) contains original dates like Order Date or Invoice Date, your reports often need to show projected or calculated dates. This process lets you create new information from your existing data.

Here are a few common scenarios where you'll need to add days:

• E-commerce Shipping: Calculate an "Estimated Delivery Date" by adding your average shipping time (e.g., 5 days) to the Order Date.
• Project Management: Determine task deadlines by adding a set number of days to a project's Start Date.
• Sales & CRM: Set a "Follow-Up Date" for a new lead by adding 14 days to the Contact Date.
• Finance & Billing: Figure out the "Payment Due Date" based on your company's net terms (e.g., adding 30 days to the Invoice Date).

In all these cases, you’re creating a new, valuable business metric that doesn’t exist in your original data set, helping you monitor operations and track performance more effectively.

Understanding the Basics: Using DAX for Date Calculations

Power BI uses a formula language called DAX (Data Analysis Expressions) to perform calculations. If you've ever written a formula in Excel, like SUM(), you're already familiar with the core concept of an "expression". The same principle applies here, so there's nothing to be nervous about.

All date manipulations, including adding days, are done using simple DAX functions. We’ll be creating what’s known as a calculated column. This is a new column you add to your data table in Power BI, where each row's value is determined by a DAX formula you write. Let's dig in.

Method 1: The Go-To Choice is using the DATEADD Function

The DATEADD function is the most robust and versatile way to handle date calculations in Power BI. While we’re focusing on adding days, this function can also handle adding or subtracting months, quarters, and years, making it an essential tool for all things dates.

Understanding the DATEADD Syntax

The formula structure for DATEADD is simple:

DATEADD(<,dates>, <,number_of_intervals>, <,interval>)

Let's quickly break down its three parts:

• <,dates>,: This is your starting date column, like [OrderDate] or [InvoiceDate].
• <,number_of_intervals>,: The number of units you want to add. This can be a positive number to add time (like 5) or a negative number to subtract time (like -5).
• <,interval>,: The unit of time you're adding. For our purposes, we'll use DAY. Other options you'll start using later are MONTH, QUARTER, and YEAR to add those measures respectively.

Step-by-Step Example: Calculate a Shipping Deadline

Let's imagine you run an e-commerce store with orders table data and you want to create a new "Ship By Date" column that is three business days later than the OrderDate.

Step 1: Open Your Report in Power BI

First, open your Power BI Desktop file and ensure your orders table is loaded. Our example table, called Orders, contains a column named OrderDate.

Step 2: Navigate to the Data View

On the left-hand side of the Power BI window, click the "Data" icon (it looks like a small table). This will show you the raw data in your tables.

Step 3: Create a New Calculated Column

With your Orders table selected, you'll see a “Table tools" menu at the top. Click on New Column. This will open up the formula bar where you will input your DAX formula.

Step 4: Write and Enter the DATEADD Formula

In the formula bar, give your new column a descriptive name followed by an equal sign. Let's call it ShipByDate. Then, type in the DATEADD formula to add 3 days to your OrderDate. Your final formula will look like this:

ShipByDate = DATEADD('Orders'[OrderDate], 3, DAY)

After typing the formula, just hit Enter.

Step 5: Format the New Column as a Date

Power BI will automatically calculate the new date for every row. You might notice the ShipByDate column shows both a date and a time. Since we just need the date, you’ll want to clean it up.

• Find the Format dropdown menu. You'll want to replace the Date / Time with a “Date” format, e.g., m/d/yyyy. This’ll hide the unnecessary time part of your date.

Method 2: Using the Plus Operator (+)

If you're only adding days, Power BI offers a more direct, shorthand method using the + operator. This is because Power BI represents all date data as Date / Time.

Let us use the same column and just switch to using the + operator. We'll call it ShipByDate_Alt.

Steps to Use the Plus Operator

Step 1: Create a New Column

Create your ShipByDate_Alt as a column. In the formula bar, type:

ShipByDate_Alt = 'Orders'[OrderDate] + 3

Hit enter after writing this. Power BI will calculate the new date for each order, adding 3 days directly.

This method uses basic arithmetic instead of more complex DAX functions.

Practical Tips for Common Issues

• Data Types Matter: Ensure your date column is correctly formatted as Date. If not, you may encounter errors. Use Power Query to transform your data type if necessary.
• Handling Blanks: If your data includes blanks, use DAX functions like BLANK() or IF() to manage how they are handled.

Final Thoughts

We hope you now feel confident using DAX to calculate and transform data in Power BI. These tools are invaluable for creating insightful dashboards and reports across different departments.

Using comprehensive DAX functions can mean the difference between standard and advanced analytics. For further efficiency, check out platforms like Graphed, which offer integrated solutions for managing your data seamlessly.