One of the most commonly requested items in Tableau dashboarding is to show your metrics as Year to Date (YTD), Prior Year to Date (PYTD), and Year over Year change (YoY).
Although seemingly pretty simple, there can be a ton of different variables that can affect how you should calculate these metrics. In this blog I am going to walk through some of those variables as well as how I set up my calculations to make these fields easier to calculate.Â
Things to Consider
Before setting up your YTD and PYTD calculations there are a few basic questions you should ask. Every dataset is different and every reporting need is different, so let’s dive into these questions first to figure out what we’re working with.Â
Data Structure - What is the basic structure of your data? What does each row of this data represent. If each row in your fact table represents one transaction or event, and the date corresponds to that event occuring, then you're in luck. Make sure the date field you're looking at isn't what time the data was entered or updated. Data like appointments can have scheduled dates into the future, make sure to understand how all of your date fields function within your data set.
Timing - How often is the data refreshed? Data that is refreshed once a week means that you need to look for max dates or end of week dates rather than just using a TODAY() function. Data that is refreshed twice a day means you will need to look for a max timestamp and not just a day in order to not duplicate your data. Make sure to understand the data refresh schedule.
Reporting Structure - What is the required timeframe structure for your KPIs? If leadership only wants to see your YTD numbers through the end of the previous week, you aren't going to want to include your data for the most recent day if it doesn't fall in under that criteria, even if the data is refreshed daily and in an appropriate structure.
Metric Definition - What is the defined time period that we are reporting on? I have seen my fair share of clients try and report on a weekly basis only to realize that the number of days in their weeks are off from year to year or that
There are most likely going to be other factors to consider before setting up your YTD calculations, but these are a few that I’ve found to come up repeatedly with clients. Now let’s start setting the foundation for your calculations.Â
YTD Using Max Date
The first thing to do while setting up your YTD calcs is to set up a Max Date calculation. Given that you have a date instead of a week number for your time period reference, this is going to give you the max date in your dataset.Â
//Max Date
{MAX([Transaction Date])}
I’ll often create a Max Year field just to specify what year we’re referring to as well.Â
//Max Year
{MAX(YEAR([Receipt Date]))}
//Max Year-1
{MAX(YEAR([Receipt Date]))}-1
Next, we want to create a set of Booleans that will return TRUE if the date referenced is within our desired timeframe and FALSE if it’s not. Here are some of those calculations. The assumption for these calculations is that data will be updated daily so that we will be getting the most up-to-date data by using our Max Date/Max Year calculations.Â
//Max Year TF YEAR([Transaction Date])=[Max Year]
//Max Year-1 TF YEAR([Transaction Date])=([Max Year-1])
Often users will want a line chart showing the YTD and PYTD numbers compared to one another. To create a T|F filter use the calculation method below.Â
//CY or PY [Max Year TF] OR [Max Year-1 TF]
Creating the PYTD calculation is a little harder since there are a couple of different variables to take into account. You can either calculate PYTD in terms of days into the year or today’s date, but last year. Leap years can throw off your calculations by a day if you’re doing the today’s date but last year methodology.Â
In the calculation below I’ve decided that I want today’s date but for last year to be my PYTD calculation date.Â
//Previous Year to Date
[Date] <= DATEADD('year',-1,[Max Date])
AND
[Date] >= DATE("1/1/"+STR(YEAR([Max Date])-1))
These calculations have been using the assumption that the Max Date in your data will give you the date that you want to calculate YTD with. Since this is not always the case, let’s look at some other methods for calculating YTD.
YTD Using TODAY()
For this use case, we are assuming that our data still updates daily but that doesn’t mean that we’ll have new entries on a daily basis. This distinction needs to be made because going off of the max date in your data can leave you with incorrect YTD v PYTD numbers. Let’s say that you want to create a YTD measure but you haven’t had any new data entries in the past 5 days. Let’s say in the previous years you had several new entries on those dates (but in the previous year), those entries will now be counted using this method. Let’s see how we can set up our YTD and PYTD fields using our TODAY() function.Â
The current year formula will look just slightly different and will not change our outcomes all that much. Although it might be tempting to just put YEAR([Transaction Date])=[Max Year] we need to make sure that we don’t have future dates in our data as well. If you know for a fact that this will not be possible for your data set then you can exclude the [Transaction Date]<=TODAY() statement.Â
//Current Year [Transaction Date]<=TODAY() AND YEAR([Transaction Date])=[Max Year]
Now let’s look into the PYTD formula. If you are using TODAY() or TODAY()-1 as your YTD date, you will have a much different measure than using the {Max(Date)} method.Â
//Previous Year to Date
[Transaction Date] <= DATEADD('year',-1,TODAY())
AND
[Transaction Date] >= DATE("1/1/"+ STR(YEAR(TODAY())-1))
As briefly mentioned earlier, one use case for this type of calculation is appointments data, or data that is schedule to happen but hasn’t yet. Your fact table might include all completed and scheduled appointments. This means that if you were to try and use our first method of {MAX(Date)} then you might be accounting for dates in the future. If you have appointments that should only be counted if they’re scheduled for today or before then using this method will help account for those data entries.Â
YTD Using Max Month End
Lots of financial reporting is done as of the most recent month end. Even though your data might be updating more frequently than that, you will only want to show data as of the month recent month end. Let’s dive into how to set up your data filters to show just this data.Â
The logic we are using in this filter is that the month you are currently in (aka Month(Today())) will not be the month that you want your reporting to show. You will want to show the previous month, always. So if today is March 1st, 2021 you will want to show data through the 28th of February, 2021, and if today was March 31st, the same dates would be desired.Â
//Most Recent Month End IF Month([Max Date])=1 THEN Year([Transaction Date])=[Max Year-1] ELSE Year[Transaction Date]=[Max Year] AND Month([Transaction Date])< Month([Max Date])
Essentially, if your data includes data from January, we want the results from all of last year. If not, we want the year of the transaction date to be the same as the Max Date in the dataset and the month of our transaction dates to be less than the month of the Max Transaction Date month.Â
One issue using this formula will be if you get data that is updated daily, but there are not records for the most recent dates. You will need to replace the [Transaction Date] fields with TODAY() functions. See the formula below.Â
//Most Recent Month End IF Month(TODAY())=1 THEN Year([Transaction Date])=Year(TODAY())-1 ELSE Year[Transaction Date]=Year(TODAY()) AND Month([Transaction Date])< Month(TODAY())
To get the previous years dates up until the same point, use the slightly modified calculation below.Â
//Most Recent Month End | PY IF Month(TODAY())=1 THEN Year([Transaction Date])=Year(TODAY())-1 ELSE Year([Transaction Date])=(Year(TODAY())-1) AND Month([Transaction Date])< Month(TODAY()) END
Use this field either in your filter pane or your calculation to get data for your current year and month recent month end.
Rolling 12 Month Period
Instead of looking at YTD or PYTD metrics, sometimes you will be asked to analyze the most recent 12 months v the previous 12 months period. Let’s look at how to do this.Â
An easy way to frame this requirement is to say “how did the previous 365 period compare to the 365 day period before that”. This way you are set on using a 365 day period instead of saying “a year”.Â
//P365 Days
[Transaction Date]>=DATEADD('year',-1,[Max Date])
If you’re using the Today() method, use the formula below.Â
//P365 Days
[Date]>DATEADD('year',-1,TODAY())
AND
[Date]<=TODAY()
To find the previous 365 days before this, use the formulas below.Â
//P2(365) Days
[Transaction Date]>=DATEADD('year',-2,[Max Date])
AND
[Transaction Date]< DATEADD('year',-1,[Max Date])
Or
//P2(365) Days
[Transaction Date]>=DATEADD('year',-2,TODAY())
AND
[Transaction Date]< DATEADD('year',-1,TODAY())
Use the P365 days field to find the previous year period metrics and the P(365) days field to find the metrics for the 365 days before that.Â
Most Recent Completed Week
Just like the YTD field being up until the most recently completed month end, if you’re doing any weekly reporting you will want a way to specify your YTD up until the most recently completed week.
//Most Recent Completed Week Week(TODAY())-1 >= WEEK([Date]) AND [Max Year TF]
//Most Recent Completed Week PY Week(TODAY())-1 >= WEEK([Date]) AND [Max Year -1 TF]
Weekly reporting is extremely common, so make sure you’re limited your dates appropriately when doing it.Â
Using Time Period Fields
Sometimes your data will come pre-aggregated at a week or a month level. Because there might not be an actual date field to use means that you need to use some unique formulas to determine YTD and PYTD.Â
First let’s identify our exact scenario. In this case your data is aggregated to the weekly level by year. To make it easier, we are going to assume that there’s going to be at least one data entry per week.
A common format of these aggregated views is to have the weeks come in as YYYYWW or WWYYYY. You can use the LEFT/RIGHT functions to parse out the week and year into separate fields if necessary.Â
First we need to create a field that grabs your max year number.Â
//Max Year
[Year]=({Max([Year})
//Max Year-1
[Year]=({Max([Year])}-1) Now it might be tempting to create a {Max(Week Number)} field, but that is just going to give you 52 or 53 (depending how their weeks are counted) because we are assuming that there is at least one full year of previous history. Instead we are going to create a field called Max Week | Max Year that finds the max week of the current year.
//Max Week | Max Year
MAX(
IF [Max Year]
THEN [Week Number]
END)
//The [Max Year] field is from above Now let’s create our T|F filters for the YTD and PYTD fields using the week numbers.
//YTD TF [Max Year] AND [Max Week | Max Year]
//PYTD TF [Max Year-1] AND [Max Week | Max Year]
To do the same exercise but with monthly data you would just substitute in the month numbers for the week numbers.
Past 4 Weeks & Past 13 Weeks
Another common ask from end users is to provide totals or averages from the 4 or 13 weeks preceding the most recent week. This allows you to analyze your most current performance with other recent performance trends without using artificial barriers such as year (a 13 week lookback from February 2021 will include December 2020 data).
There are a couple of ways to accomplish this, so let’s take a look one one method. In this case I am going to be comparing the Current Week with the 4 Weeks that proceeded it. (Although this is not technically a YTD calculation it’s a common date related calculation ask).
Using the TODAY() method, we are going to identify that we want to look at the 4 weeks before the most recently completed week (which we identify as Max Week -1). See the calculations below.Â
//Max Year TF Year([Date])=Year(TODAY())
//Before Full Previous Week Week(TODAY())-1 > WEEK([Date]) AND [Max Year TF]
//4W before Full Previous Week Week(TODAY())-5 <= WEEK([Date]) AND [Before Full Previous Week] AND [Max Year TF]
To get a PYTD indicator for this same method use the Max Year -1 TF calculation in place of the Max Year TF field and then create a [Before Full Previous Week PY] field like below.Â
//Before Full Previous Week | PY Week(TODAY())-1 > WEEK([Date]) AND [Max Year -1 TF]
//4W before Full Previous Week PY Week(TODAY())-5 <= WEEK([Date]) AND [Before Full Previous Week | PY] AND [Max Year -1 TF]
One potential issue with this calculation right now is that is we are the TODAY() function as the max of our date. Make sure to substitute the correct max date values for your use case.Â
Fiscal Years, Reporting Years, Custom Years, Etc.
This entire blog we have been operating under the assumption that we are using a calendar year to calculate YTD and PYTD. Alternative reporting years are extremely common in financial reporting so we definitely needed to touch on it. The most common examples of fiscal years differing from calendar years is a fiscal year that goes from Jun of Year 1 to July of Year 2.Â
The easiest way to deal with this is to have a dates table and bring in the translated fiscal year for every date (we will cover dates tables in a later section). This way you can reference these fiscal year fields in all of your calculations. If you are not able to utilize a dates table, Tableau has some built in functionality for fiscal years. You can find more at this link.Â
In short, in the worksheet view you can right click on the data source in the top left and select Data Properties from the pop out menu to specify your fiscal year starts and what weekday your week starts on.Â
Another way to handle custom date periods is to create long IF THEN statements that assign your specific dates to a fiscal time period. Here are a couple of link detailing some of those calculations:
Using a Dates Table
One of my preferred methods to doing YTD and PYTD calculations involves far fewer steps than any of the methods mentioned above. This method is using a dates table. Most companies will have some sort of enterprise dates table they maintain that detail things about the dates past and future. If possible, build in a YTD and PYTD flag into your dates table that will output a T|F.Â
Bring in this dates table to your data and then relate it to the dates field in your fact table. Now your calculations to produce a YTD metric are ridiculously easy. Take a look at the calculation below. By relating our fact table dates to our dates table, we can now reference the YTD Flag in that table to identify if our fact table dates should be part of our YTD or PYTD calculations.Â
// YTD Sales SUM(IF [YTD Flag] THEN [Sales] END)
As referenced previously, a dates table that already has YTD and PYTD fields, including the fiscal year varieties, would be the best way to create these calculations
Finalized YTD and PYTD Calculations
Now that we’ve created all of these YTD, PY, and PYTD fields we can finally create the metric calculations. Having creating the time period fields beforehand will make the final calculations a lot easier. As a reminder, in IF THEN calculations in Tableau, a boolean (T|F) will default to equals TRUE when stated alone, so IF [YTD] THEN [SALES] END will read as if [YTD] = TRUE then [SALES].Â
// YTD Sales SUM(IF [YTD TF] THEN [Sales] END)
// PYTD Sales SUM(IF [PYTD TF] THEN [Sales] END)
// YoY Sales ([YTD Sales]-[PYTD Sales])/[PYTD Sales]
We can use any of the T|F fields that we’ve created in this blog to make our final metric calculations. The above calculations are just examples of that.Â
