How to use DATEADD for Period-Over-Period Analysis in Excel

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.

The ```DATEADD``` Function

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.

Understanding our Data Model

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.

What is 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…

Range

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.

Attributes

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

Set up

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.

Demo time!

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”.

Creating our 2nd measure with 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…

Analyzing Period-Over-Period changes

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. …

How to measure a percentage increase or decrease with ```DATEADD```

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.

Cleaning up months with no data

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!

Refactoring with variables

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.

```DATEADD``` is smart!

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.

How will you use ```DATEADD``` in your own data sets?

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!