Often, we want to compare the figures of the current time period to some past period – a key case study is comparing the sales of the current month to the previous month or same month in the previous year.

There are ways to do this using various DATE functions, and knowledge of advanced astrophysics (ok no astrophysics required), but today we’re going to look at a simple method using the LOOKUP function.

Comparing Current to the Previous Period

Let’s take a well-known type of chart – Sales over time:

Here I have total sales for each week in a given year. Now suppose that instead of the total sales of each week, I want to see how each week’s sales compare to the previous week. In this case, I want to look up the sales for the previous week.

Let’s do that using the LOOKUP function in a new calculated measure as follows:

In the LOOKUP function, we’ve included the measure we’re calculating and where we are finding it. Here, the -1 look up the sum of sales for the row before the current row – in this case, the week before the current week in the date/time dimension.

Here’s what our new chart looks like where every week is showing its difference in sales to the previous week:

Quite revealing isn’t it? We can clearly see a strong up and down fluctuation as the year progresses.

Comparing to the Same Month in a Previous Year

Sometimes we want to compare the current sales to the sales of the same month in a previous year. Well, let’s think about this – if I’m viewing sales by month, I can simply adjust the value in my lookup to look back ‘12’ month as follows:

Below I have sales by month on the top, and the difference of sales by month compared to the same month in the previous year on the bottom. Notice the bottom chart has no values for 2022 because the 2022 months have no 2021 data to compare to.

Awesome! Now we can easily compare current to previous days/weeks/months/quarters/years by simply adjusting the time axis and how far back we want to look.

Dynamically changing the date level

So far, we’ve been using fixed values in the LOOKUP function like -1 and -12. These work when we are looking at date at a fixed level in its hierarchy like months or weeks but not changing between the two.

I’d really like to change between the two or change from years to quarters and quarters to months, then perhaps to weeks.

To do this we need to make that value in the LOOKUP dynamic. And a simple way to do that is to count the number of rows within the date dimension. At the year level, the count will typically be around 365, for quarters around 90, for months around 30, and so on.

Note that this method works when you have a full year of data – i.e. all 365 days.

Now, let’s create a calculated measure to give the appropriate value to look behind based on the level of the date hierarchy:

Each row tests for a particular level of the data hierarchy. I’ve estimated conservatively, so I used 200 for years – just in case our year data is missing some days. And I’ve done the same using 50 for quarters (even though quarters should have around 90 days), 15 for months and 5 for weeks.

I can now change the hierarchy level of date in my worksheet and the chart will calculate its lookup accordingly.

Viewing quarters:

 

 

Viewing weeks:

 

And that’s all! Now we have a powerful, yet simple, tool to compare a measure in the current time period to a previous time period.

 

The Data School
Author: The Data School