Creating a Period to Date Matrix in Tableau

Today, we will talk an idea I recently had for working with "Period to Date" values.  Tableau can easily create PTD values.  Here, I create daily, "Month to Date", and "Year to Date" values in a text table.  As usual, we will use the Superstore Sales sample data set in Tableau.

Period to Date Text Table

If I want to add more measures, I can add them to the columns.  However, the client wanted the table in the following format:

Period to Date Matrix Mock-Up

Here, the report date is always the previous day.  This means that we no longer have a time dimension in our graph.  Also, our measures are not on the same shelf as our table calculations.  How can we accomplish this?

Step 1:

• Create the following calculated field

Period to Date Calculated Field

This field applies the label "Daily" to every record on the "Report Date", "Month to Date" to every record in the Month of "Report Date" (but not to those that already have the "Daily" label), and "Year to Date" to every record in the Year of "Report Date" (but not to those that already have the "Daily" or "Month to Date" labels).  A sample of this data, if the report date was 1/1/2012 would look like

Calculated Field Mock-Up

Step 2:

• Add "Period to Date" to the "Columns" Shelf
• Filter "NA" out of "Period to Date"
• Add your measures to the "Text" Shelf

Period to Date Matrix

An astute reader might say, "Wait!!! Month to Date doesn't look at the complete month, and Year to Date doesn't look at the complete year."  You're right!  The "Month to Date" column is missing the "Daily" values and the "Year to Date" column is missing the "Daily" and "Month to Date" values.  This sounds like a running sum issue.

Step 3:

• Change each measure to a running sum across the row

Period to Date Matrix

If we look back at the first picture in this post, we can see that the "MTD Sales" value at the end of January is $340,627, which matches our "MTD Sales" value here for the month of January.  This is evidence that our logic is correct.  Now, we've created the exact chart the client wanted and they have showered us with giant piles of money (I may or may not be exaggerating).

One issue with this method is, it requires that the measures be additive, or at least made up of additive components.  Feel free to play around with this method.  You can create a parameter or calculated field that would allow you to change the "Report Date" to your liking.  I hope you found this informative.  Thanks for reading.

EDIT: I found an issue with this methodology.  If the data date is the first day of the month, first month of the year, etc., you will not have "Month to Date" or "Year to Date" in your data set, respectively.  The workaround we used was to move the Period-To-Date function down into the SQL query and union hard-coded rows onto the data set for each missing value of the Period-To-Date function.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn