The Scenario:

Recently, a client asked the Data School to give them a dashboard with a sales overview comparing sales of the most recent week, compared to sales in the exact same week, the previous year.

This caused me some frustration, and so to avoid you that same frustration, here is how to do the same/similar analysis.

First, you need to calculate the ‘max date’, the following calculation should suffice.

DATE(DATEPARSE ( “yyyy-MM-dd”, STR({MAX([Order Date])}) ))

This tells calculation tells Tableau to search all the available dates in your [Order Date] field, and works out which is the maximum.

To find out the sales figure for the max week, the following calc should work:

IF DATETRUNC(‘week’,[Order Date]) = [01. Max Date] THEN [Sales] ELSE 0 END

Essentially, this calculation is simply telling Tableau to show only sales figures for our max week. The current version of Superstore gives you 0, so if you’re following along with Superstore and see 0, don’t worry.

If you want to compare with the previous week sales, then you’ll need the following two calculations:

DATETRUNC(‘week’, DATEADD(‘week’, -52, [01. Max Date]))

IF DATETRUNC(‘week’,[Order Date]) = [Max Date – 1 Week] THEN [Sales] ELSE 0 END

Our first calc is just telling Tableau that we want to look at the same week as our max week, but 52 weeks in the past (just adjust this figure in the calc if you want to compare to different weeks).

The second calc is simply following on and saying ‘give me the sales for that week’.