How to Isolate Comparison Date Ranges in Tableau

Comparing Dates with Tableau’s DATETRUNC, DATEADD, and LOD Functions

Get more from dates in Tableau by learning how to: (1) automatically roll up dates to the current day, week, month, quarter, or year, (2) isolate a comparison date range, (3) parameterize the date part selection for comparison ranges, and (4) use level of detail expressions to calculate period over period changes.

Calculated field that isolates the current period:
IF DATETRUNC([Date Part],[Order Date + 365]) = DATETRUNC([Date Part],TODAY()) THEN “Current Month” END

Calculated field that isolates the comparison period:
IF DATETRUNC([Date Part],[Order Date + 365]) = DATEADD([Date Part],-1,DATETRUNC([Date Part],TODAY())) THEN “Comparison Month” END

Calculated field to classify current period and comparison period:
IF [Current Period] = “Current Month” THEN “Current Month”
ELSEIF [Comparison Period] = “Comparison Month” THEN “Comparison Month”
ELSE “Not in Range”
END

Calculated field to isolate Current Month’s Sales in the Sample – Superstore dataset:
{EXCLUDE [Order Date + 365]: SUM(IF [Isolated Date Classifications] = “Current Month” THEN [Sales] END)}

Calculated field to isolate Comparison Month’s Sales in the Sample – Superstore dataset:
{EXCLUDE [Order Date + 365]: SUM(IF [Isolated Date Classifications] = “Comparison Month” THEN [Sales] END)}

Calculated field to compare Current Month’s Sales to Comparison Month’s Sales:
(ATTR([Current Month’s Sales])/ATTR([Comparison Month’s Sales]))-1

Hi. This is Ryan with Playfair Data TV. In this video, I’m going to show you how to dynamically isolate period over period date ranges. What I mean by dynamic is we’re going to have these date ranges based on today’s date. So you could think of a month over month comparison. I’m going to show you how to pull and isolate just this month’s data, so that we can compare it to just last month’s data.

This is one of the handiest tactics you can implement in a business. And I’ve just kind of realized over time that most of the videos here at Playfair Data TV, I kind of take a shortcut and I parameterize the month over month selection. But in the real world, it’s much better if that’s dynamic. So that’s what I’m going to show you how to do here today.

So in order to do so, there are a couple of small changes that I need to make to the Sample Superstore dataset, so that you can follow along. The first is the Order Date field in the sample dataset as of Tableau version 2020.2, which is what I’m recording this in, currently only goes through the year 2019. And I’m recording this video on May 24, 2020. So what I’m going to do is make a simple calculated field out of my Order Date field that adds a year to it. So I’ll call this Order Date plus 365. And that actually is the formula. We’re going to take our Order Date, and we’re going to add 365 days to it. That’ll make our data run through 2020.

The other small change I’m going to make is I’d prefer that my weeks start on Monday and end on Sunday. In Tableau by default, weeks start on Sunday and end on Saturday. If you want to change that, click Data in the top navigation, hover over Sample Superstore and click Date Properties. That will allow you to change the week start to Monday.

All right, let’s also make a quick chart that looks at sales by month of order date plus 365. If this piece is new to you, there are videos here at Playfair Data TV to help you. Obviously, how to make a line graph, that’s my second favorite chart, you can find that over on the Fundamentals track.

I’m also going to convert this into a dual-axis combination chart. There’s a video here to show you how to do that as well. But I’m going to do it now so that we can see and do some QA-ing on whether or not we’ve correctly isolated the current month. So what I’m going to do is duplicate the Sales pill on the Rows Shelf, convert the second row to circles, and make this a dual-axis combination chart, synchronize my axes, and make these circles a little bit larger. All right.

So there’s all of our foundation in place. Now, for the tactics– and I will share all these formulas in the related content below the video. So don’t feel like you have to pause and follow along and type all this out. You can simply copy and paste below the video.

But for the first one, I’m going to call this Current Month Sales. Or I’ll just call it Current Month for now. And we’ll keep building on this. So Current Month, what we’re going to say is IF DATETRUNC. And then what I need next is my date part. So if we’re starting with month, I’m going to type tick mark, month– those should always be lowercase– comma, my order date plus 365 equals DATETRUNC month again. And then instead of a field from my dataset, I’m going to use this dynamic function called TODAY(). So if month of today, open parenthesis, close parenthesis– whoops, I left out an equal sign here. So if those things equal each other, then this is my current month and END. All right.

So this is the first key to dynamically isolating date ranges. It’s this DATETRUNC formula. If that’s new to you, I mention this quite a bit here Playfair Data TV. But if you click on anything that is blue, it will look it up over here in this little data dictionary. If you don’t see that, just click this arrow. So sometimes it might start out like this, depending on what version of Tableau you’re using. If you click that arrow, you will see that when I clicked on it, it looked up that function. It gives me a little definition of what it is doing and an example of how to use it in the syntax.

What we’ve done so far for the current month is we’ve said look at the month of order date 365 and see if it equals the month of today. When you truncate a date, it essentially rolls up all the different days in– really, it’s technically rows. But when we’re dealing with dates, I think of this as it’s rolling all of our days into the first day of the month.

So we’ve said, if I’m recording this on May 24, it’s saying does May in the order date plus 365 equal May from today? Let’s see if it worked. I’m going to click OK. And add this to the Color Marks Card of my circles. So Current Month added there. The current month is orange. Everything else is null. And that’s what I’d expect. It can only be the current month for one of these 48 months in the Sample Superstore dataset. And if I hover over, it is in fact May 2020. So so far, so good.

Let’s move on to isolating my previous month. So I’m going to duplicate Current Month and edit this one and call it my Comparison Month. And most of the elements of this are the same. But we’re going to have one key addition, which is another function called DATEADD. We’re going to say if the date truncated at the monthly level from the Order Date in the Sample Superstore equals– we’re going to keep all this the same, but we’re going to subtract 1 month just for this first example. So we’re trying to create a month over month comparison.

And the function is DATEADD, open parenthesis. Once again, we need our date part. This time it is month again, comma. But notice, this is the key part of this, Interval is the next thing I type after the comma. When you’re subtracting a month, we need a minus sign. And I’ll put a 1 for 1 month and then comma. And then we need one more parentheses at the end. And we will call this Comparison Month.

All right, let’s test this one out. And again, I’ll share this in the Related Content. I know it looks like it’s getting a little messy. But these are relatively simple calculated fields. If you ever see an error, it’s probably due to a lack of parenthesis. Perhaps you have a comma in the wrong spot. But I’ll copy these, and you can just copy and paste them out from the Related Content below the video.

I’m going to click OK. And instead of Current Month, I’m going to drag Comparison Month onto Color. And we should see that dot move one spot. And we did. If I hover over to confirm, we’re in April 2020.

Now, let’s combine some of these together so that we can see both on the same view. I’m going to start another calculated field. And let’s say Isolated Date Classifications. If Current Month equals Current Month, then– we’ll call this Current Month– ELSEIF Comparison Month equals Comparison Month, then Comparison Month. And then, we’ll do a catchall that says ELSE Not in Range. If you don’t type that last ELSE statement, it just gets classified as Null, which is also fine.

So this might seem a little– a little silly and repetitive to say current month equals current month then current month. There’s two routes to go on how you write these. If you’d prefer, you could put the entire string of logic in this formula. So that would be this– if DATETRUNC equals month of Order Date equals DATETRUNC month of today. So that would replace this logic here. It’s up to you. There’s pros and cons to that.

The latter approach is much more explicit in what is classifying things as current month. The way that I have it now is a little bit more elegant. The reason that I typically do my calculated fields this way is I might want to use these original, I call them, root calculated fields. So I might want to use those Current Month and Comparison Month fields in other ways. So I’ve isolated those. And those are kind of my elements that I can then build in other calculated fields and use them to make it bigger and bigger versus if I had to retype all the logic, this might turn into a 10 to 20 lines of code for one calculated field, just a little bit harder to troubleshoot. But that’s up to you.

Let’s test it out and see how it’s looking. So instead of coloring the circles by just one thing, I’m going to color them by our new classification calculated field. And we now should see three colors, and we do. We see Current Month is colored orange. Comparison Month is colored blue. And everything else is not in range. So so far, so good.

Now, I’m going to show you a couple of fun tricks. So the first thing, if you wanted to, you could do not only a period over period comparison, but you could do a year over year comparison. That would be very simple to get to from here. I would just go into my comparison calculated field, and I would change the 1 to a 12. So we’re adding– or technically subtracting 12 months. If I click OK, we should see that blue circle move from where it was in April 2020 all the way back to May 2019. So orange is May this year. Blue is May last year.

I’m going to undo that. That’s the first thing you can do with these calculations. The next thing you can do is you can parameterize the date part. So instead of always looking at just a month over month comparison, you could change this to look at day over day, week over week, month over month, year over year. I’m going to show you the first three of those.

So I’ll create a parameter. And I’ll call this my Date Part. And it will be a data type of String and a list of allowable values. I’ll do day, week, month. It’s very important if you want to implement this tactic that your values for your date parts are lowercase. As you saw previously with the DATETRUNC and DATEADD calculated fields, it was not a coincidence that those date parts were lowercase. They have to be lowercase in order for those particular functions to work correctly. So make sure your values are lowercase.

The Display As can be whatever you want. You can make them title cased. You could abbreviate them if you wanted to with just the first letter. But I’ll just stick with title casing. And I’ll call this good.

I’m now going to parameterize a couple of things. My Order Date plus 365, I’m going to create a calculated field. And I’ll call this Date with Date Part. There’s another video here that shows you in a little more detail how to do this. And it even shows you how to change the date part by clicking on buttons if you want to check that out. But just a very quick review of it, it’s DATETRUNC, tick mark, my Date Part parameter– sorry, no tick mark, because this is already a data type of String– comma and close the wrapping after your order date.

I’m going to click OK. Before I put that on the view, I’m going to change the current value of the Date Part parameter to month just to make sure this doesn’t break on me. And I’m going to replace month of Order Date plus 365 with my Order Date plus 365– I’m sorry, with my Date with Date Part. That’s the newly created calculated field we just made. I’m going to right-click, drag it to the Columns Shelf and choose the very first option– date with date part continuous. I’m not choosing a specific date part this time because that’s now parameterized. I don’t want to make it stuck in one spot, because we’re going to choose between day, week, and month. So I’ll click OK.

If I did this correctly, we should not see anything change, because we just replaced month with month. Sure enough, we don’t. So I feel good about this. I’ll show the parameter control so that we can make some changes. So there’s my date part now.

Couple other places that we need to implement that Date Part parameter. We need to go back to our Current Month. And instead of fixing this at the monthly level, we need to replace it with that Date Part parameter. So there’s spot number one. And here’s spot number two.

So this is now dynamic based on the selection in this dropdown. If I click OK, we once again shouldn’t see anything change, because we’re still looking at months. One more spot we need to change that before we change the parameter. And that’s our Comparison Month calculated field. Once again, it’s hard-coded currently, but I’ll replace month with Date Part to make this dynamic. Date Part, and there’s one more spot. OK, so three spots in the Comparison Month formula, two spots in the Current Month formula. And by the way, these are no longer comparison months. So maybe we should just call this Comparison Period and Current Period, because these will now be either day, week, or month. All right.

Once again, we still shouldn’t see anything change. Believe it or not, this is all good news so far. So everything’s intact. But now if I make a different selection– so if I look at weeks instead of months, we not only see more granularity– so it’s now a line graph looking at weeks. Let me actually limit that to maybe this year, so it’s a little bit easier to see. I’ll just stick with 2020. So not only are we seeing weeks instead of months for our granularity, but because we parameterized our Comparison Period and Current Period, we also see an orange dot for the current week, May 18. Remember, at the very beginning of the video, I changed the week starts to Monday. So we’re looking at May 18, Monday, through May 24 as our current period. And we’re looking at May 11 through May 17 as the comparison period.

If I go all the way down to today, we’ll see a lot of granularity. For this one, maybe I want to maybe isolate it to just May of 2020. So we can see this a little bit better. It looks like we have a null unfortunately. This is in the Sample Superstore dataset. But this is isolating the current day. So remember, this Current Period formula is dynamic based on today’s date. And this will update as you open Tableau. It refreshes, and it’s always looking at today’s date. So because I’m recording this on May 24, that was classified as the current period. And then we just happen to have a null for yesterday, because we got so granular with this dataset.

This is the way that I really like to do this, because we’ve classified all of our dates with this formula. From here, there’s quite a bit you can do. So one of the examples is you could isolate just the current month’s sales by doing a formula such as Current Month’s Sales. So IF our classification– so this is really the formula that makes this whole thing go– equals Current Month then Sales END.

And then I will just sum these up. So current month sales, I’ll click OK. I’ll also get this back to looking at months, so I can test some of this out. So current month’s sales should be $45,211. On a new sheet, I will add my Current Month’s Sales, which is now a new measure. And sure enough, we see 45,211.

I could do the same thing for previous month’s sales just by duplicating current month’s sales and changing this to Comparison. Click OK. Let’s throw that on the view. We should see an answer of 34,657. I’ll go back to my QA sheet and sure enough, 34,657.

Now that I’ve got those two isolated, I can do some things like comparisons. I could say Period over Period Difference and look at Current Month’s Sales divided by Comparison Month’s Sales. I’ll wrap that whole thing and subtract 1. That creates a percent change. I will apply some default formatting to make this a percentage and throw it on here as a third row. And our month over month change was 30.45%. I always like to spot check these by just opening a calculator. 34,657, and sure enough, the answer I’d be looking for is 30.45% increase. And that is correct.

One last little trick I’ll show you, those formulas that I’ve done so far are isolating the current month versus the comparison month at the visualization level of detail, which works great in a vacuum like this. I create this element all the time. It’s over on the Dashboards track. These callout numbers, where you’re comparing the current period versus the comparison period, and then either an index score or a percent change, that works well because there’s no granularity breaking our isolated date ranges. However, if I were to say put month of Order Date on this view, I’ll go back to my continuous month and then change this to discrete so that we can see this a little bit better. It should be over here towards the end.

OK, so here’s the issue– maybe this will be a little bit easier to see as bars. OK, because this time I changed the granularity– the visualization level of detail from essentially nothing– there was nothing interfering or breaking our formulas– to I changed it to month of Order Date, now we only see the Current Month show up for May 2020. And we only see the Comparison Month show up for April 2020. And because those aren’t lined up, Tableau can’t do the math to show us that 30% increase on the third row. So the entire third row is null.

So there are times where you not only want to isolate the current period and/or the comparison period, but you want that number to run all the way across the dataset, so that you can do the math and use it in flexible ways. To accomplish that, I’m going to show you one more trick, which involves Level of Detail expressions. So for this one I’m going to go into my Current Month’s Sales calculated field. And you can keep the whole root of this. But at the very beginning type open curly bracket EXCLUDE. And then we’re ignoring our Order Date plus 365 dimension. And then at the very end, typing close curly bracket.

If this syntax is new to you, of course, we have you covered here Playfair Data TV, just check out An Introduction to Level of Detail Expressions. That’ll help explain what these do. But for this one, we’re just ignoring the Order Date field. And then we’re looking for that Comparison Month Sales number.

I’m going to click OK. And we see this break. And that is because our aggregation changed. So I’m going to go to my Current Month’s Sales– oops, did I edit the– let me double check if I edited Comparison. OK, I edited Comparison first instead of Current. That’s OK. I just need to replace the red pill with our newly created one, because we’ve technically changed the aggregation. I always kind of laugh when it turns bright red like something is really wrong. But it’s simply the aggregation change, we just need to replace it. So I’m going to drop Comparison Month’s Sales right on top of what we had before.

We also temporarily see our period over period formula break. And that’s because Level of Detail expressions are unique in that they have to be aggregated again. So I could either type SUM here again, or I could type ATTR, and it would take the aggregation that’s within that formula. So I’ll click Apply. Make sure we’re in good shape.

And now, you can kind of see why this is going to come together. So because I added that Level of Detail expression to ignore essentially the columns, that second row populates so that we see last month’s sales all the way across the whole dataset. Before, it was only in this one cell. And because that cell was not lined up with the current month’s sales, we weren’t able to determine this number, 30.45%. Now that it’s populated all the way across, we can see that number.

I’d probably want to go ahead and do the same thing for Current Month’s Sales. So at the very beginning, I’m ignoring my Order Date plus 365. Whenever you add to a calculated field, sometimes Tableau will overwrite the aggregation. So if this breaks on you or you’re getting an error message, it’s probably because the SUM aggregation disappeared on us. So not quite what I wanted, but easy to get back. Just type SUM again and then close curly bracket at the very end.

Click OK. This will break again. Again, it’s just because the aggregation has changed. But now if I put Current Month’s Sales on top of what was there before and go add ATTR to my Current Month’s Sales, we should see the entire first row populate. And we do.

So that was just a review on one of the best ways to isolate current date ranges and previous date ranges. I showed you how to classify those date ranges. I also showed you how to isolate the sales values for those date ranges. And if you want to use these together in flexible ways, you can go ahead and add the optional Level of Detail expression, which ignores the Order Date field. And it truly isolates that number. So it’s kind of frozen, and you can use it in a lot of flexible ways to create valuable period over period comparisons for your business.

This has been Ryan with Playfair Data TV – thanks for watching!