It can be tricky to compare two date ranges in Tableau. I’ve done a similar tutorial in the past, but there’s more than one way to skin a cat. Especially when you’re used to the simplicity of Google Analytics or the ubiquity of Excel. This tutorial will show a way to create custom date ranges for specific metrics. Admittedly, this method is imperfect. It doesn’t allow for easy swapping out of measures, and in general lacks flexibility. But there are certain circumstances where it will certainly get the job done. Feel free to download a copy of the workbook from here.
This tutorial will demonstrate how to make two measures: a prior period and a current period. A percent difference measure using current and prior. And a year to date(YTD) measure. All four are calculated fields that use parameters to set various dates. I will be using the default superstore dataset in this example.
Parameters – There Are Five
To begin the process of making the measures, it’s best to create five parameters. These parameters are straight forward. All five are date parameters with the default settings.
The five parameters are named:
CURRENT PERIOD Start
CURRENT PERIOD End
PRIOR PERIOD Start
PRIOR PERIOD End
YTD DATE – Will be used for the YTD measure
Current Period
The current period calculated field includes a date variable in this case [Order Date] and the measure you want to use in this case [Profit].
IF [Order Date] >= [CURRENT PERIOD Start] and [Order Date] <= [CURRENT PERIOD End] THEN [Profit] END
Rather simply, this measure only shows the profit values between the two dates selected in the two parameters used in this measures.
Prior Period
Almost identical to the Current Period calculation…
IF [Order Date] >= [PRIOR PERIOD Start] and [Order Date] <= [PRIOR PERIOD End] THEN [Profit] END
The only real difference between the two is a naming convention. Prior and Current are just a naming convention. You could apply the same logic in this calculation to other measures you want to filter by parameters. But for this use case, Prior and Current makes the most sense.
Percent Difference
A table calculation could be used in place of making a completely different measure. But table calculations can be tricky to configure. Their flexibility is both their blessing and their curse. If this is a report that needs to be standardized, then there is a case to be made for using a calculated field.
(sum([CURRENT PERIOD])-sum([PRIOR PERIOD]))/sum([PRIOR PERIOD])
The equation is your usual percent difference. You could make it an absolute value if that’s needed.
YTD
This is probably the most complicated of the calculated fields created in this tutorial. The measure filters out all data that occurs after the selected date and all data occurring before the start of the year.
IF [Order Date] <= [YTD DATE] AND
DATETRUNC( "year", [Order Date]) = DATETRUNC("year", [YTD DATE])
THEN
[Profit]
END[YTD DATE] is the parameter that selects the specific date. The measure shows data that’s a year to that specific date. The If-statement creates the filtering effect. “[Order Date] <= [YTD DATE]” captures all the data occurring before the selected ” [YTD DATE] ” date. “DATETRUNC(“year”, [Order Date]) = DATETRUNC(“year”, [YTD DATE] )” only shows data that occurs within the same year of the selected ” [YTD DATE]” date.
The DATETRUNC function truncates date variables. Since the first term is “year” it outputs only the year of a given date. For example, if I passed DATETRUNC(“year”, #12/25/2014#), the output would be “2014.” So in the above equation, it makes sure that the [Order Date] year equals the [YTD DATE] year. Thus only showing YTD data for the selected date. More information about the DATETRUNC function can be found here.
Tableau has a great article on how to create YTD and month to date(MTD) functions. They use the same concept but use the TODAY() function. This function calls the current date. By using the TODAY() function the report would automatically display the current YTD or MTD values.
Putting It All Together
Pull Measure names into Columns. Pull a dimension in this case Category into Rows. Then put the four created measure into the Text Card. From here I right-clicked and selected “Show Parameter Control” on all five parameters.
For added effects:
- Right-click on the AGG(%DIFFERENCE) pill and select format. I used a custom format that shows +’s and -‘s. Here it is “”+” #,##0.00%;”-” #,##0.00%”
- Drag measure values onto Color. The right-click on it and select “Use Separate Legends.” From there you can customize the colors how you want
There you have it. Another way to compare two date ranges in Tableau.
