KNOWLEDGE BASE

Finding the most recent value of a field within a filtered date range


Published: 17 Oct 2017
Last Modified Date: 26 Aug 2019

Question

How to calculate the most recent value of a field within a filtered date range.

Environment

Tableau Desktop

Answer

Step 1 - Create the calculated fields

  1. Select Analysis > Create calculated field
  2. Name the calculated field "Set Date"
  3. Enter the following formula and click OK: 
    {FIXED [Product Name] :
    MAX (IF [Order Date] = {FIXED [Product Name] : MAX ( [Order Date] ) } THEN [Sub-Category] END)}
  4. Select Analysis > Create calculated field
  5. Name the calculated field "Subcategory Value by Date"
  6. Enter the following formula and click OK: 
    INT([Set Date] = [Sub-Category])

Step 2 - Build the view

  1. Drag "Sub-Category" and "Product Name" to Rows.
  2. Drag "Subcategory Value by Date" on Text.
  3. Drag Order Date to the Filter shelf and select "Individual Dates"
    1. Right click the date filter and select Show Filter
    2. Set the filter as desired. In this example, the filter is set as a Single Value (slider) filter. 
  4. Right click the date filter once more and select Add to context.

Additional Information

{FIXED} Level of Detail Expressions ignore Dimension Filters. Therefore, the [date] filter must be added to context.

To view the video the above steps in action, see the video below. 
Note: the video has no sound.
 

Discuss this article... Feedback Forum
Did this article resolve the issue?