KNOWLEDGE BASE

## Comparing a Measure Based on Selected Year

Published: 21 Sep 2017

### Question

How to compare a measure over time to a selected year.

### Environment

• Tableau Desktop

This example uses Superstore sample data to compare Sales by year of order date, and can be reviewed in the attached packaged workbook.

Step One: Create parameter and calculated fields

1. Right click in the data window and select Create > Parameter using the following options:
• Name : Year Parameter
• Data Type: Integer
• Allowable Values: List
• Display Format: Choose Custom and uncheck "Include thousands separators"
2. Enter all years - in this example, 2014, 2015, 2016, and 2017, then click OK.

• Create a calculated field [Selected Values] to select values for a particular year:

`iif(DATEPART('year', [Order Date]) = [Year Parameter],[Sales],null)`

• Create a calculated field [Chosen Year Values] to apply those values for the appropriate dates every year:

`window_max(sum([Selected Values]))`

• Create the final calculated field [Difference] to find the difference between each year and the fixed year chosen in [Year Parameter].

`SUM([Sales])-[Chosen Year Values]`

Note: if not comparing the sum of values, you can change "SUM" to the desired aggregation.

Step Two: Build the viz.
1. Drag [Order Date] to Columns.
2. Drag [Sales] to Rows
3. Right click on [Year Parameter] and select Show parameter control
4. Drag [Difference] to the view. In this example, place Difference on Label on the marks card.
5. Right click [Difference] on the Marks card  and select Edit Table Calculation
6. Choose Compute Using Specific dimensions > Year of Order Date