KNOWLEDGE BASE

How to display only past date reference lines


Published: 20 May 2022
Last Modified Date: 20 May 2022

Question

How to display only past date reference lines

Environment

  • Tableau Desktop

Answer

*In this case, reference line will be set to 80% of Sales as an example.
*The data you want to display is for the year 2022 and it is currently May 2022, 
 so you want to display only the reference line from January 2022 - May 2022
IMAGE
User-added image

<Steps>
1. Connect to Sample -Superstore.
2. Right click and drag [Order Date] to Columns > Select MY(Order Date)
3. Drag [Sales] to Rows.
4. Change Marks type to Bar.
5. Drag [Order Date] to Filters > Select Years > Check in 2022.

6. Create Calculated Field as following.
    Name : Reference line
    Calculation : [Sales] * 0.8
7. Create Calculated Field as following.
    Name : Reference line filter
    Calculation : IF DATE(DATETRUNC('month',[Order Date])) <= DATE(DATETRUNC('month',TODAY()))
                  THEN [Reference line]
                  ELSE NULL
                  END
8. Drag [Reference line filter] to Detail.
9. Click Analytics pane.
User-added image
10. Drag [Reference Line] to view and drop to Cell.
User-added image
11. Setting as following.
User-added image
 
Did this article resolve the issue?