Data Viz Done Right

September 27, 2013

Tableau Tip: Creating a chart that only displays the last day of each year, quarter or month

Scenario - You're doing quarterly financial reporting, you have sales by day, you're in the middle of the quarter and you only want to show the last day of each of the previous quarters (assuming your sales are cumulative by day).  You also want to allow the user to view the data by Year, Month or Day.

This can all be accomplished through a calculated field.  

Step 1: Create a line chart of sales by day.  I also changed the format of my time axis to \Qq 'yy and removed the date axis title.

Step 2: Create a parameter to allow the user to choose the level of detailed for the chart. In this example, I'm allow the user to view the data by Year, Quarter, Month or Day.

Step 3: Create a calculated field based on the parameter selection to only return the previous day of each year, quarter or month.  I wanted this to return a Boolean that I can add as a filter.

Step 4: Verify the calculation works by adding it to the Filters shelf and keeping only 1.  

As I pick each option in the parameter, the chart updates accordingly.

Download the workbook here.


  1. You can simplify your formula to:

    dateadd('day',1,[Order Date])=DATETRUNC([Date Level],dateadd('day',1,[Order Date]))

    keep only when True, and get the same results, if you setup your parameter so the string values are all lowercase, and you can still have the display value of the parameter be proper case.

    1. Thanks Joe. Will Tableau run quicker with this calc or is just neater?

    2. In theory potentially faster because there is no extra CASE statement, but in practice, likely not a noticeable difference in speed. I would argue that speed of a human being able to parse the formula would be speed factor worth paying attention to in this situation. Maybe for some, thee IF statements nested inside of a CASE statement is easier to parse. Thankfully Tableau allows us multiple paths to the same result, none are necessarily wrong, just different, and differences can be good. :)

    3. How would you modify this to show mtd for current month while in month view?

    4. You could create a T/F calculation that filters the dates to only the current month.


  3. how can you create a T/F calculation wherein you can compute for the moving average for the past 11 months? for example i want to compute for the moving average for March 2014 without including the year dimension in my viz? Thanks

  4. Have you tried using a DATEDIFF calc at the month level? That would be an easy way to find the last 11 months.

  5. in tableau compare last 15 days profit,last15days profit on each every month year?and also last 5 days sales for every year?