In the business intelligence realm, it's hard to find data that doesn't have some kind of date component.

And good luck finding an executive who isn’t keenly interested in how their business is performing relative to last year, last month, or even yesterday!

In the past, this kind of analysis would usually involve layers of complex formulas in an Excel spreadsheet, while leaving everyone’s favorite Excel reporting tool, Pivot Tables, out of the picture entirely.

But thanks to Microsoft Excel’s Time Intelligence functions, we can now perform powerful date-driven analysis - things like year-to-date sales, month-over-month comparisons, or 30-day moving averages – all right inside of Pivot Tables.

And the good news is, it’s WAY easier than you might think!

In this tutorial, I’m going to walk you through how to use the `DATEADD`

function, for period-over-period analysis (with examples), so let’s dive in!

Sidenote:If you’re struggling with this feature, or simply want to improve your Excel skills, then check out my Business Intelligence with Excel course.

It’s the only course you need to launch your career as a Data Professional!

You’ll learn to master Excel's built-in power tools, including Power Query, Power Pivot Tables, Data Modeling, the DAX formula language, and so much more.

You'll even build an end-to-end Business Intelligence solution for a fictional Surf Shop where you'll create dozens of KPIs that provide deep insight into business performance.

By the end, you'll have the skills to get hired as a Business Intelligence Analyst in 2024!

Check it out here or watch the first videos for free.

As an added bonus, not only do you get access to the course content, but you can also ask me, and other students questions in our private Discord channel.

With that out of the way, let’s get into how to use `DATEADD`

.

`DATEADD`

is part of Excel’s Business Intelligence-focused DAX formula language, and is used to compare measures and calculations across different timeframes: month over month, year over year, etc.

It does this by manipulating the filter context in which a DAX `CALCULATE`

function operates.

More specifically, `DATEADD`

adds or subtracts a specified number of date intervals to a given cell, or “address”, within a Pivot Table.

This means that the “filters” applied to the calculation in that cell aren't determined by the year, month, or date in the adjacent column, but rather **by the logic of the DATEADD function instead**.

To get a better idea of how all this works, it’ll probably help to take a sneak peek ahead at the Pivot Table breakout we’re ultimately trying to get to.

This pivot table is broken out by year and month in rows, and has a metric called “Total Revenue” in the values area.

There’s also a metric called “Revenue Previous Year”, so let’s dig into that one a little deeper.

**For example**

If we look at the row for July 2019: the revenue for that month is $314,000.

But, if you look at the value in the “Revenue Previous Year” column, you’ll see a completely different number.

However, if you look back exactly one year in the “Total Revenue” column to July 2018, THAT number matches our “Revenue Previous Year” value for July 2019 perfectly.

So what’s going on here?

As you might have guessed, this “Revenue Previous Year” is being generated by a `DATEADD`

function.

This means that any number you see in that column results from taking that revenue metric, or measure, and changing its filter context in the Pivot Table. Specifically, by moving that filter context forward or backward in time a specified number of intervals.

In this case, the “number of intervals” is exactly one year, but it’s important to note that the `DATEADD`

function is highly flexible, so you could also move the filter context backward six months or forward three months - the choice is yours.

So now that we know what `DATEADD`

can do, let's see how we can use it to build out a view like this.

Before we break down how to use this, let's take a moment to understand the very simple data model we will be working with.

Our Data Model includes just two tables:

- An
`Orders`

table that tracks customer orders, and - A
`Date`

table containing a single row for every date in our timespan of interest

If you've never used time intelligence functions in DAX, you may not be familiar with the concept of a date table.

In short, it's a table that's intended to contain - at the very minimum - a row for every date within whatever our timespan of interest is.

**For example**

If our data spans 2020 and 2021, then our date table would need to have one row for every individual date in those two years.

Some other interesting points…

We can actually extend the dates in the date table beyond the scope of the dates in our actual data as well, but this isn't strictly necessary.

Date tables also frequently contain additional handy attributes related to those individual dates - things like:

- The month
- The year
- The quarter, and
- The day of the week

Date tables are so important when using “Power” Excel, that there are actually multiple ways to create them.

You can utilize an expression in Power Query, DAX code in the data model, or simply import a text or Excel file with the dates you want to include in the table.

But whatever approach you take to create your date table, it won't do you much good unless it's connected to the data tables in your data model via relationships.

**For example**

In our data model, the Dates table and Orders table are joined via a one-to-many relationship, with the Date field from the Dates table on the one side, and the `OrderDate`

date from the Orders table on the many side.

This makes sense, considering we know that our date table should have exactly one row per date, but that many customer orders could conceivably occur on a single date.

All right, I think that's more than enough background information; now it's time to get down to writing some DAX code.

So to get us started, I'll set up a simple “Power” Pivot Table, based on our little data model, with year and month on rows…

Now our goal will ultimately be to use the `DATEADD`

function to recreate that “Revenue Previous Year” view we saw a moment ago.

But before we get to the fun stuff, there’s just a smidge of setup to get through.

Namely, we need to create a measure that simply calculates total revenue (no fancy filter context-altering or time intelligence functions required).

So, we set up a measure as usual:

Then we can drop that into our Pivot Table - simple enough so far, right?

So with our “base” measure in place, we’re ready to use `DATEADD`

to help us calculate - for any given intersection of year and month in our Pivot - the value of that measure from one year prior.

To do that, we'll also create another new measure, called “Revenue Previous Year”.

`DATEADD`

The actual DAX formula for this measure will start with a `CALCULATE`

function, which will take our existing “Total Revenue” measure as its first argument.

Then, by feeding our revenue calculation into `CALCULATE`

, it will enable us to apply a custom filter to that measure at a given set of coordinates in a Pivot Table.

This is where `DATEADD`

comes in.

We’ll use `DATEADD`

for the second argument to `CALCULATE`

, which defines the filter that will be applied to the expression/measure used in the first argument.

It looks like this:

That brings us to the syntax of `DATEADD`

itself.

Now as is usually the case with time intelligence functions in DAX, the first argument is just going to be the column from our date table that contains the actual individual dates.

For the second argument, we need to tell the number of intervals - intervals of time, to be exact - that we want to walk forward or backward in the context of our Pivot Table.

**For example**

If we want to compare the current month's revenue against the revenue from six months ago on the same row of our pivot, we need to offset from any given address in the Pivot Table minus six months.

Hence, the second argument to `DATEADD`

in that scenario would be **-6**.

In our case, however, we want to go one year back.

This means we could either set the interval to be minus twelve months (-12), or we could equivalently go with minus one year (-1). That second option seems a little more straightforward, so let’s opt for that.

Now for the third and final argument, we need to tell `DATEADD`

the interval of time that we're offsetting forward or backward from a given address in the Pivot Table - years, months, etc.

In our particular case, we're going back in time one year, and as a result, our interval will be just “year”.

Note that “`YEAR`

” is entered without quotation marks around it, which is what a normal text string would require.

**Why is this the case?**

Simply because `YEAR`

- along with certain other words that represent intervals of time, like `MONTH`

, and `DAY`

, are all recognized special keywords that DAX, and therefore the `DATEADD`

function, recognizes.

So at this point, we can close out the parentheses for our `DATEADD`

function, and start adjusting our `CALCULATE`

function:

Format the measure as Currency, then hit “Check Formula” to make sure we didn’t make any typos, and finally click OK to lock in our measure.

Now for the fun part - adding that measure to our Pivot Table, and checking if it works:

Boom! Our August 2018 revenue number matches our predicted August 2019 revenue number.

So now that it works, let’s look at ways we can use this…

One cool application of being able to see the output of a measure for two completely different points in time side-by-side in a Pivot Table, is that **we can create additional metrics that show how that measure changed from one timeframe to the next**.

**For example**

Let’s say, we wanted to measure a percent increase or percent decrease.

With our “Total Revenue” and “Revenue Previous Year” measures already set up, layering that kind of analysis into our current example is a relatively simple matter. …

Start off by creating a new measure, called “YOY Revenue Delta”.

Our formula for this measure will simply subtract the previous year's revenue from the current year's revenue, and then divide the result, to give us that percentage increase/decrease.

As a best practice, and to handle any potential division errors, I’ll use DAX’s `BLANK`

function for the “alternate result” argument of `DIVIDE`

:

Since this measure is going to return a percentage, I'll format the measure as a number, and then a percent, before finally clicking OK and adding it to our example pivot.

So let’s see if this worked.

Just eyeballing through the values in our Pivot table, we see that these percentages make sense.

**For example**

In August 2019, revenue was a little over $400,000, which is in fact about a 54% increase over the previous August's revenue of $264,000.

This ability to compare metrics over different time frames side-by-side is extremely valuable and also represents an incredibly common task in the world of Business Intelligence.

This sort of view would take a lot of work to set up without the power of time intelligence functions, but as we just saw, with DAX it's really just a matter of setting up a few relatively simple formulas.

There’s a quick housekeeping item that we need to attend to before we move on.

Our `DATEADD`

function is producing some rows in our Pivot Table which, while technically valid, don't exactly make for a great user experience.

**For example**

Our dataset only goes out through August of 2021.

But for September through December of 2021, there is obviously such a thing as a previous year's revenue value - because we did have data for September through December of 2020.

But of course, this means that our year-over-year comparisons are going to always yield **-100%** for those months because we're going from whatever revenue we had in the corresponding month from the previous year to nothing.

Now that's clearly a pretty confusing user experience!

So what we need to do, is to modify our Revenue Previous Year measure to return a `BLANK`

if our Total Revenue measure (i.e., the current month) is blank.

We can do this by testing whether the Total Revenue is blank using DAX’s `ISBLANK`

function. If it is in fact blank, we’ll return a blank value in turn via the `BLANK`

function. Otherwise, we’ll just Revenue Previous Year we've already created.

Here’s what the formula looks like:

Now looking at the updated Pivot Table it appears that our little tweak with `ISBLANK`

was enough to get rid of those rogue blank rows in our Pivot Table!

Now if you want to get really crazy, and are only interested in seeing the year-over-year delta and not the actual revenue numbers from the current and previous year, you can actually generate that calculation with a single formula!

**For example**

One approach to this could be to nest the formulas we’ve already written inside of each other. And while this would technically work, it would also be very difficult to read, reason about, and update or maintain.

A better approach is to capture the logic of the component metrics – revenue and previous year's revenue - in variables, and then simply plug those variables into our final formula that crunches out year-over-year delta.

Let's see what that would look like:

As you can see, we first dropped the output of our calculations for revenue and the previous year's revenue into their own variables. Then, after the `RETURN`

keyword, we simply plug those variables into the formula for % change year-over-year.

Our YOY Revenue Delta measure now stands on its own, since all the logic it needs is captured in those variables - so we could even delete our original measures if we wanted.

One thing I'd like to emphasize is just how smart the `DATEADD`

function is.

**For example**

Let’s say we were trying to compare numbers for one February to those of a previous February. However, thanks to leap years, one of those Februarys had 29 days instead of 28.

In this scenario, DATEADD is smart enough to recognize that these months have different numbers, and still compare the entire total for each.

Likewise, if we were comparing entire years to each other, and one of those years - being a leap year - had 366 days instead of 365, DATEADD would compare the entire total for both years. It's not going to get confused by the fact that the years have a slightly different number of days.

The bottom line is that you can generally trust the DATEADD function to be smart enough to handle time periods with different numbers of days in them, and still make accurate period-over-period comparisons between these timeframes.

As you can see, the `DATEADD`

function is an incredibly helpful and powerful tool for time period analysis.

Be sure to give it a try and use it in your own data project, and glean those insights.

**Fun fact:** As awesome as `DATEADD`

is, there is so much more to DAX time intelligence functions – and DAX in general – than I could ever cover in a single tutorial.

To learn more about DAX, “Power Excel”, and many other Business Intelligence skills like VBA, SQL, and Python, check out my Business Intelligence with Excel course.

Or you can even watch the first videos for free here.

**Bonus:** By joining ZTM and taking my course, not only do you get access to the course content, but you can also ask me, and other students questions in our private Discord channel 24/7 - so you’ll never get stuck!