Last Modified Date: 31 Dec 2019
AnswerThis 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
- 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"
- 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:
- 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.
- Drag [Order Date] to Columns.
- Drag [Sales] to Rows
- Right click on [Year Parameter] and select Show parameter control
- Drag [Difference] to the view. In this example, place Difference on Label on the marks card.
- Right click [Difference] on the Marks card and select Edit Table Calculation
- Choose Compute Using Specific dimensions > Year of Order Date
Discuss this article...
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials