We are thrilled to announce that Unilytics has joined Optimus SBR
Learn more

Posted by Eric

April 10, 2017


Leave Reply

Tableau Master Challenge #3

Calculating Measures in Prior Years

Tableau makes many things significantly easier, but not everything. One very common challenge Tableau designers wade through is computing prior period values. For example, sales data for prior periods that align with the current year to date (YTD).

To further illustrate the example, if the current YTD is only 128 days into the year (May 8 on non-leap years), it is frequently necessary to compare the same 128 days from the prior year, the year before that, etc. This situation gets even more complicated when you consider some organizations work on a calendar year, while others work on a fiscal year.

This challenge has several possible solutions, so any answer that can meet the result constraints is a good one. A single solution is provided with this Master Challenge, which you can use to evaluate your own solution, if it happens to be one of the other possible solutions

To attempt this Master Challenge, it will be necessary to use the Superstore sample data that ships with Tableau. In this challenge, the solution will be based on the Superstore sales data that covered four years from 2010 through 2013 and had a grand total sales of $8,951.931.32. If you need a copy of this file, you may find it here.

To solve this challenge, the following requirements must be met: Superstore – Sales

  • Allow the user to select calendar year, or fiscal year for the calculation of “prior period”.
  • Allow the user to enter the date on which the fiscal year starts.

In addition, the following outcomes must exist in the solution:

  • Based on the selection of calendar or fiscal year, compute the sales for the same period in all four years of data.
  • Also based on the selection of calendar or fiscal year, compute the sales for the entire year.

To align with the solution, configure your workbook as follows:

  • Filter the data source to include data through May 8, 2013.
  • Set the default fiscal year to start on October 1st.

Note the following exceptions in the data:

  • When fiscal year is selected, the first year will be incomplete as it starts part way through the fiscal year.

If you assemble all the pieces correctly, you should arrive at the following results:

For fiscal year (starting October 1st):

For calendar year:

For extra credit, attempt to create the following visual:

That’s it! It may sound easy, but many Tableau designers have quite a considerable struggle with this calculation. Best of luck taking this challenge, and when you’re ready to see the solution, you will find it, plus the solution workbook, here.

Ask one of our Tableau Pros if you need help solving this Tableau challenge or any other assistance with Tableau.

If you’re interested in significantly improving your Tableau skills, we offer Tableau Training courses including a Tableau Master Class for  advanced  users.

Leave a Reply

Your email address will not be published. Required fields are marked *

Explore Posts By Category



Want to know more?

Contact us