Simple Calculations for YTD/YoY, QTD/QoQ, MTD/MoM and WTD/WoW in Tableau

[ Followup guest post by Hans Romeijn: Calculating Period-To-Date/PoP with Indicators for Better Performance ]

Year to Date (YTD) and Year over Year (YoY) calculations are very important in business dashboards. Jim Dehner recently wrote a great post on the topic. It inspired me to present additional approaches for the issues.

Calculating YTD and YoY without LOD

We have been using YTD and YoY long before LOD (level of details) appeared. So we are not dependent on LOD at all. 

Here is how we calculate YTD, YTD Sales, Previous YTD, Previous YTD Sales and YoY Change %. Note that YTD and Previous YTD can be applied to any other measure's YTD calculations.

An example of using the above formula is provided below and here is the workbook.

What is good about LOD?

With LOD, such as below, it provides portability when using the same value in different sheets with different dimensions. (Caveat: make your filters contextual when using LOD). If you use it in a single sheet, LOD is not a concern. In most cases, we don't need LOD.

Formatting YoY

When calculating YoY change % using the native quick table calculation "Percent Difference", we find that the first column is empty. Visually it's not an agreeable thing, although we perfectly understand why. With a little formatting, we can make the viewers feel better like below. 

Double click the green pill of Percent Difference and use ZN() to wrap around the formula. Then format the pill as follows.

This will put a dash "-" in the empty  column. If you wish, we can put a N/A there. 

Calculations for QTD/QoQ, MTD/MoM and WTD/WoW

Note that you can replace the date part 'year' in YTD/YoY calculations by  'quarter', 'month' or 'week' in both DATEDIFF() and DATEADD() for calculating QTD/QoQ, MTD/MoM and WTD/WoW. Here are the calculations for QTD, Previous QTD, QoQ Change % and respective sales. You can apply QTD and Previous QTD to calculating other measures.

Calculations for QoPYQ: QTD over Previous Year QTD

The condition for the previous year QTD is like 

We also provide calculations for PoPYP: PTD over Previous Year PTD. P is a parameterized period which can be quarter, month or week.

The companion workbook can be downloaded here.

Date Grain and Anchor Date as Parameters

PS. Tom T left a comment below saying that we can use a parameter for the date grain like year, quarter, month and week. That's very true. It will allow user to select a date grain. So here is the parameter Date Grain:

And here is the Period to Date calculation, Period being defined by the Date Grain.

Also Tom also suggested we can use a parameter for the anchor date instead of Today() as in Period to Date. So here is the parameter Date Select:

And here is the PTD Select Date calculation.

And Previous PTD, Period over Period Change % etc are included in the companion workbook. Check it out.

Feel free to leave comments below or contact me at twitter @aleksoft .