I believe there is more than one way to calculate a Year to Date vs. Prior Year to Date comparison in Tableau. Like in most cases with Tableau there are several ways to Rome.
We’re using the Superstore sample data and start by calculating a Level of Detail (LOD) expression for the Most Recent Date: {FIXED : MAX([Order Date])}. When we drag this field (discrete) onto rows, we only get the last date of our data set displayed. Next up we’re calculating the first date of the current year (CY) with DATETRUNC(‘year’, [Most Recent Date]). We also want to calculate the Most Recent Date of the prior year (PY) with DATEADD(‘year’, -1, [Most Recent Date]) – so basically our most recent date minus one. We’re repeating this step for the first date of the year so that we get the first date of the prior year. So when we drag all our new calculated field onto rows it should look something like this:
So let’s get to the values. We’re calculating the CY Sales: IF [Order Date]>=[First Date CY] AND [Order Date]<=[Most Recent Date] THEN [Sales] ELSE 0 END. If we put that on the Text Shelf in the Mark Card we’re getting a single value displayed. It always makes sense to double check if that value is correct. For the PY Sales we simply duplicate the CY Sales and change the CY fields (First Date and Most Recent Date) to PY fields. The next step is calculating the difference between CY and PY Sales: SUM([CY Sales])-SUM([PY Sales]). We can then also change the number format of our last three fields to currency. In addition to that, we can also calculate the percentage of change over the two years: [CY PY Difference]/SUM([PY Sales]) and then set the number format to custom: +0.00%;-0.00%.
We should end up with something like this:
Obviously we can make this look all nice and presentable as BANs for example. But that’s really up to you, now that you know how to calculate a YTD vs. Prior YTD comparison.