Published: 20 May 2022 Last Modified Date: 20 May 2022
How to display only past date reference lines
*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
<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.
10. Drag [Reference Line] to view and drop to Cell.
11. Setting as following.
Thank you for providing your feedback on the effectiveness of the article.