How to accomplish Latest Date in Tableau? When to use Today() and what are the problems with it. What is the alternative? Read the article for clarification.

When talking about Dates, broadly we want the latest Date, we tend to use Today(). But this is not always the right choice because the current day does not need to be the Latest Date in the Database.

Consider a Store, and there will be certain days when it is closed. Now on the day that the store is closed, what is our Latest Date in your Database? Yesterday right. Will Today() help here?

Another scenario. We have a Database which gets populated every Sunday. In the mid of the week, what will be our Latest Date? Will Today() help you get out of the scenario?

In one of my Projects, we were having a Forecasted Data for that year and I was asked to get the Latest Date. Now, in the Data, I have a Date exceeding Today(). Even here Today() is not going to help.

So, when we are talking about Latest Date, consider it as getting the largest out of a group of Numbers (Dates are a special type of numbers called Julian Numbers). Just compare the numbers with each other and get the largest number, and when it is Tableau, we have MAX function to get the Greatest or Largest.

Hence, using Max(Date Field) will give the Latest Date. In the image below, I have used Super Store Data and applied Max on the Order Date field.

With only Max(Order Date) field in Worksheet.

Now, see the image below and try to understand what is happening to Max(Order Date) field and why is it now showing the same day as Order Date, and why is Today() showing Current Day for all the Rows.

Try to think about what is happening in the background.

Now, Order Date and Max(Order Date) are giving the same values throughout. But as per what we have established earlier, Max(Order Date) should show 30–12–2019. Also when it is Today() why do we have the same date for all the Rows? Pause the reading for a while and implement this in your Tableau Desktop (or Tableau Public) and try to guess why is this happening.

Now, I know you have got what is happening in the background but let me elaborate so that we both are on the same platform. Tableau is a “ROW SLAVE”. That is because any manipulation that you do in Tableau will always and always happen at the Row Level only. Even when you aggregate all the Row Level values are aggregated to show the value it shows.

The same is happening in the above image. When we say Max(Order Date), if there are no other fields in the View, it is giving 30–12–2019 comparing all the Rows that are present in the DB. The granularity is at the DB level.

The moment I bring the Order Date field beside it, now the granularity is at the Order Date. We have one Date per Row in the View. So Max is calculated per Row and every Date is Max to itself (Also Min to itself). Hence you are getting a different Max Date for every Row which is the same as the Order Date value in that Row.

Now we come to Today() Column. Here if we observe, there is some value for every Row. This is because whatever may be the Date in the Order Date field but Today is current Date right. IF we want to use something in place of Today(), it should get the same characteristics of Today() like it should be the same value for all the Rows like in Today().

Note: This is somewhat complex to understand. If this explanation doesn’t satisfy you, please mention your doubt in the comments so that I can give more explanation to it.

Is the behaviour of Max(Order Date) is same as Today()?
Can we use Max(Order Date) in calcualtions as Today()?
Is the Granularity of Max(Order Date) and Today() same?

In LODs, there is something called TABLE SCOPED LODS. That is nothing but a fixed LOD without a Dimension part in it.

{ Fixed : Aggregation(Measure) } or { Aggregation(Measure)}

They just take what ever Measure with aggregation that you put in them and their Granularity is maintained at the DB level .

Note: Here DB level is used in a broader sense. Fixed LODs are effected by Data Source FIlters, Extract Filters and Context Filters.

If I say { Sum(Sales) }, they add up all the Sales and the value is shown for every row in the View.

The same way when I say { MAX(Order Date) } then for every row, it will show the same Order date which here is 30–12–2019.

If you are still confused, think of ROBO movie by Rajinikanth. When I want to compare all the people with Rajinikanth, I will create so many ROBOs equal to the number of people and keep one besides every person and compare.

Observer the Last column on Right.

Using LODs, we can get the Latest Date to behave the same as that of Today(). Now you can use it in all places where you used Today() earlier.

P.S — I tried to simplify the concept as much as I can but if you still didn’t understand any point in it, please mention in the comments so that I can get back to you with a proper explanation.