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 .

(Refresh the page if you want to view the gif image multiple times. Or go to Tableau Public and click the button at the top-right corner.)

Jake and I collaborated on a dashboard. He told me that he learnt a way to create an in-place help page in Tableau. He first saw it at a conference somewhere and couldn't recall who the speaker was. So I am blogging here about it but the credit goes to somebody else. If anyone knows who the original creator is, leave a comment below.

The key idea is to float a semi transparent worksheet on top of the dashboard, where a help text box is strategically placed on top of each chart. This way, we can explain how to view each chart and what data points are important, etc. This worksheet is collapsible by a show/hide button.

Below I would like to show how this worksheet can be constructed.

1. Sheet with a single data mark.

Double click the empty space in Marks panel and add two single quotes. Make the null pill a text label. This creates a single null mark.
Set the view as "Entire View"

2. Create an show/hide button

Go to the target dashboard
Drag a floating vertical container to the dashboard, making it cover all the area of interest.
Drag the Single Null Mark sheet and drop it into the above container. Hide the sheet title.
Create an open/close button for the container and place the button at the top-right corner.