Tableau – Dynamic Year over Year KPI with Trend Arrows

In this guide, I will walk through how to create a dynamic KPI that updates based on a year parameter. It will also contain trend arrows and a percent difference to enable clear year over year analysis. Trend arrows are powerful addition to KPIs, offering instant visual insights by assisting the user to quickly grasp whether a metric is improving or declining based off the previous year.

Create a Year Parameter

1. Create a new parameter
2. Set the date type to String
3. Change the allowable values to List
4. Set the values and display as to the years
5. Select OK
6. Right click on the newly created parameter and select ‘Show Parameter’

 

 

Create a Filter for the Parameter

1. Create a new calculated field
2. Rename the calculation e.g. ‘Year Filter’
3. Input the calculation: YEAR([Order Date] = INT([Select Year])
4. Select OK

This sets the order date field to match the selected value within the parameter. As you can’t compare integer and string values, I’ve wrapped the select year parameter in an INT() function.

 

 

Add the Filter to your View

1. Drag the parameter filter to the filters shelf
2. Select true
3. Click apply

This will now set the values to dynamically change based off the selected year from the Year parameter.

Calculate Current Year Sales

1. Create a new calculated field
2. Rename the calculation e.g. ‘Selected Year Sales’
3. Input the calculation: IF YEAR([Order Date])) = INT([Select Year]) THEN [Sales] END
4. Select OK

This calculation only returns the total sales from the selected year. Since the parameter is a string, I wrapped it in an INT() function as you can’t compare string and integer values.

 

Calculate Previous Year Sales

1. Create a new calculated field
2. Rename the calculation e.g. ‘Selected Year Sales’
3. Input the calculation: { FIXED : SUM(IF YEAR([Order Date]) = INT([Select Year]) – 1 THEN [Sales] END ) }
4. Select OK

Similar to the previous calculation, this one only retrieves total sales from the previous year by subtracting 1 from the selected year parameter.

 

Calculate Percentage Difference Between the Years

1. Create a new calculated field
2. Rename the calculation e.g. ‘YoY % Difference’
3. Input the calculation: (SUM([Selected Year Sales]) – SUM([Previous Year Sales])) / SUM([Previous Year Sales])
4. Select OK

Create the KPI Indicators

1. Create a new calculated field
2. Rename the calculation e.g. ‘YoY KPI Arrow’
3. Input the Calculation:
4. IF ISNULL([YoY % Difference]) THEN “”
   ELSEIF [YoY % Difference] > 0 THEN “▲”
   ELSE “▼ ”
   END
5. Select OK

This if statement checks the difference between the two years. If it is positive, it will display an upward arrow, otherwise it will show a downward arrow for negative values. It also checks to see if the difference is null, otherwise there will be no indicator. To explore more arrow symbols on Windows, you can use the Character Map application. It contains a wide range of arrow characters if you’d like to customise the visual further.

 

Creating the KPI

Now that all of the calculations are complete, we need to drag the fields onto the sheet to build the visual.

Click and drag the following calculations onto Text within the marks card

1. Selected Year Sales (Total for the year selected from the parameter)
2. YoY % Difference (Percentage difference compared to the previous year)
3. YoY KPI Arrow (Visual indicator showing whether sales increased or decreased)

 

Formatting the Text

1. In the marks card, select text
2. Rearrange the fields so that the KPI Arrow calculation appears before the percent difference
3. Add supporting text like ‘vs Previous Year’ after the percent difference
4. Select Apply

 

Now we have a KPI that shows the total sales for the selected year, along with a dynamic percentage change with arrow indicators to show whether sales have increased or decreased.