How To Filter Values for the Second/Latest Date in One Table for Each ID

KNOWLEDGE BASE

How To Filter Values for the Second/Latest Date in One Table for Each ID

Published: 23 Dec 2020
Last Modified Date: 23 Dec 2020

Question

For each ID, How to show only the value of the latest date and the date before it.
For example for the data below, how to show 12/06 and 12/02 values for A and 12/05 and 12/03 values for B in the same table.

Date	ID	Value
12/1/2020	A	2
12/1/2020	B	1
12/2/2020	A	3
12/3/2020	B	2
12/6/2020	A	5
12/5/2020	B	1

Environment

Tableau Desktop

Answer

Create a calculation field to calculate the second latest value as LOOKUP(SUM([Value]),-1)
Add [Value] and [Second latest value] field to the view and set the table calculation for [Second latest value] as below.
Make a filter to show only the latest date value for each ID. The filter calculation as: MAX({FIXED [ID] :MAX([Date])})=LOOKUP(ATTR([Date]),0)
Add the filter into [Filters] shelf and choose True.