For the past few months, I’ve been working with a client for whom tracking performance is a matter of life and death. Okay, maybe it’s not that dramatic … but they care a lot about player engagement and revenue streams for the video games they produce. It turns out that keeping their fingers on the pulse of several key metrics is critical to their success, so lo and behold that is part of what I’ve been helping them do.

Along the way, I’ve noticed myself using a lot of date calculations to satisfy their needs for both fixed-time and arbitrary-time comparisons. My plan is to unveil the building blocks of these calculations – without getting too far into the messy weeds of being overly specific – in the hope that you might benefit by taking the spirit of these calculations and putting them to use in your own analysis.

Since there are numerous applications at play here, I will create multiple posts to study each one. This post is going to focus on:

Comparing your latest week to the same days of the previous week.

Uh … what?

Okay, so before I show the calculations, let me explain exactly what this means and why we’ve found it to be useful.

Let’s say your week begins on Monday and ends on Sunday. Now, let’s assume that you don’t want to wait until the week is over to compare your performance to the previous week. What do you do?

You could do many things, such as comparing your week-to-date against the entirety of the previous week. I don’t think this is a great comparison, especially in the gaming industry where engagement and revenue is generally WAY higher on the weekends and not uniformly distributed across the week. I strive to avoid hearing (and causing) the anguished screams of managers whose dashboards compare apples to oranges.

We decided to always compare our current week’s performance to the previous week’s performance, not including days of the week that have not yet come to pass within the current week.

In other words, if we have data up until Wednesday of this week, we should only compare Monday-Wednesday of this week to Monday-Wednesday of last week. Sounds easy, right? It is if you know which calculations to use.

Let’s start with the ingredients of what we’ll need to make this work. I’m a fan of breaking my calculations into organized pieces when possible, rather than having one monolithic formula that’s difficult to make sense of.

Step 1: Calculate the Maximum Date in Your Database

This is the foundation that all our date calculations will be based upon. The maximum date reflects the most recent date associated with data in your database.

You can think of the formula as saying, “Let’s fix this value to always be the highest date in the database.”

Step 2: Calculate the Start of Your Most Recent Week

DATETRUNC essentially allows us to zoom out from any given date, and in this particular case it is used to determine the exact week any given date value occurred. The DATETRUNC function is front and center in nearly any date-related task I do with Tableau.

The DATE function appears only because I don’t want this date stored as a DATETIME field. I don’t have a time stamp in this data set, so I only need to capture the date. It’s a personal preference.

Step 3: Calculate the Start of the Previous Week

DATEADD allows us to add time intervals to our date fields. If I want to calculate the previous week, then my interval is negative (move backward relative to the date) rather than positive (move forward relative to the date).

Translated into regular English, this formula says, “Let’s find the date that is exactly one week earlier than the ‘Latest Week.’”

Step 4: Calculate a Desired Metric for the Previous Week ONLY and Its Relevant Days ONLY

Whoa, now. We just went from one-lined formulas to … this. Let’s walk through what it says, step by step, so you can pick it apart and reassemble it however you need.

In plain English, the first two lines ask, “Does the date tested fall within the date range of the previous week?” That’s it.

On to the third line! What we have here is the logical “and” statement, which means there is another condition that needs to be satisfied for our data to make it out of this formula alive.

The second condition begins and ends with the open parentheses (line 4) and the closing parentheses (line 12).

Before explaining what this says, I should let you in on one of Tableau’s date nuances. In this formula, we use the DATEPART function, and this function spits out the day of the week as a number. We have Sunday (1), Monday (2), Tuesday (3), Wednesday (4), Thursday (5), Friday (6) and Saturday (7). You may see the DATEPART output to you as text if you test it in a worksheet, but just know that underneath the hood this value is referenced numerically.

A lot of businesses start their week on Monday (not Sunday), so in the formula above the number 1 (Sunday) is actually the end of the week, and 2 (Monday) is the beginning of the week. I know that may be counter-intuitive, but you’ve come this far … we’re almost finished!

At a high level, the second condition says, “Hey, is the weekday for our most recent transaction in the database a Sunday? If it is then we have a full week and thus we tag all our data as ‘True.’ However, if the weekday of our most recent transaction is NOT Sunday (meaning the current week is NOT complete), then we should only tag weekdays ON or BEFORE the tested weekday as ‘True.’”

If the tested date value did indeed occur in the previous week, and it was tagged as “True” in that second condition, then this calculated field grabs the “Sales” value. Any date value that does not meet both conditions perishes in the way of the guy who “chose poorly” in “Indiana Jones and the Last Crusade.”

Step 5: Test to Make Sure the Calculation Does What You Want

The formula we just created is reflected by the numbers furthest to the right in the table above. We can see that there are only sales values for days which occurred in the “previous week.” Further, that week only has sales values for days up until our MOST RECENT weekday.

The current week does not have data beyond Friday, so our formula does not capture data for any day beyond Friday.

We now have a calculated field that can be used to create an accurate comparison of our current week’s performance versus the previous week’s performance on a completely equal scale!

From here, you can get much more complex in how you use this information as well as how you visualize it, but that gets into tedious specifics. I hope this gives you a starting point from which you can build valuable week-on-week comparisons!