TL;DR

• Build as much date logic into your calculations as possible, as opposed to using the Filters Shelf.
• Put your reference date (e.g. TODAY() ) into its own calculation so it can be easily changed in the future if needed.
• Centralize your date logic into Boolean calculations, then reference these in your fields like YTD Sales.
• Try to structure your documentation to show these hierarchies referencing centralized logic when possible.

Should I Filter or Build in the Logic to a Calculation?

One of the main considerations in dealing with date calculations and associated metrics like year-to-date amounts is whether this should be accomplished by setting a filter to restrict the view to a certain timeframe or baking the logic into a calculated field. I almost always do the latter. Let me show you the reason why.

If I filter to a specific year, I only get access to those rows of data in the sheet I am working in (highlighted in grey below). This means that if I wanted to also show a year-over-year delta between 2019 and 2018, I cannot do so since 2018 has been filtered out of the view. In the second view, where the logic is built into the fields CY and PY Cost, I can create a view that gives me my 2019 cost and also compares to the prior year, since all rows of data are still included in the view:

TL;DR: Build as much filter logic into your calculations are possible.

How Should I Build out Date Logic Calculations?

There are a few things that come up repeatedly with clients that have driven my thoughts on this subject:

1. Future flexibility
2. Clarity of documentation

We will start with #1, which will answer the question of how best to set up your date calculations and dependent calculations. To do this, let’s first think about what is probably a poor way of setting up calculations, which (in my opinion) is a workbook of calculations that all look like this:

So why is this not best practice? A couple reasons:

1. If a client—internal or external—wants to change the comparison date away from simply TODAY( ), it is a hassle to replace all those functions across what can be dozens of calculations.
2. If you need to change the logic involved in your calculation, such as PY MTD needing to be changed to PMTD, again, you would have to replace this logic in decentralized functions.
3. I would have to type out or copy/paste the date logic into every relevant calculation; I am lazy and a poor typist.

How can we make this better? Start by putting your reference date, e.g. TODAY(), in its own calculated field:

This way, when a client wants to add additional options or change the comparison date, you can simply change the calculation, and it will cascade through the subsequent dependent calculations. Or you can right-click the today calculation and Replace References over to the new comparison date calculation, e.g.:

Let’s think about the second and third points above, which basically describe calculations that are tied into decentralized logic. In this situation, I would have to open each calculation here and change the associated logic within:

If I tie my calculations to more centralized logic in a calculation like this, the change is simple: I can adjust the logic in the relevant dimension field, and it will cascade through subsequent calculations:

So to recap, a right-sided situation is easier to manage than left:

All right, now to the second major point I mentioned earlier: documentation. As a consultancy that doesn’t typically hang around on client sites, clear documentation is paramount. And for the end user of that documentation, it is ultimately much easier to navigate calculations that have a clear hierarchy of logic than the decentralized scenario we mentioned above.

Why Documentation in Tableau Matters

When processes are well documented, the reader can see what calculations share the same base date logic, and when an issue seems to arise, they can then pinpoint what logic may be causing the issue. I typically try to structure my documentation to show these hierarchies when possible. Maybe something like this:

• YTD DATEDIFF(‘year’,[Order Date], [today])=0 and DATEPART(‘dayofyear’, [Order Date])<DATEPART(‘dayofyear’, TODAY())
  • YTD Profit if [YTD] then [Profit] END
  • YTD Sales if [YTD] then [Sales] END
  • YTD Quantity if [YTD] then [Quantity] END

Another Tip

If you are dealing with a pretty simple dashboard, it is sometimes nice to just create a date field that will go to the granularity a user selects from a parameter. This can be done in a few simple steps:

Make a parameter whose values are lowercase strings of text: day, week, month, quarter, year (for example):

Make a date field whose formula is: DATETRUNC(<date_part_parameter>, <date_field>), and use this field as a Date Value at the DAY level:

I hope these few tips help you manage the madness in your next Tableau project!