4 Replies Latest reply on Oct 14, 2018 7:42 PM by ShivaRam Chennapragada

# Variance % Against Last Year

Hi Everyone,

I am trying to replicate the below graphs that were created in Excel. The purpose is to compare sales data by category against last year. I have the year dimension that has the 2 year values, but I don't know how to create a calculation to see the % variance from last year to this year (bottom graph). I can easily reproduce to the top graph, by just plotting the year against the sales \$ measure. But I don't know how to product a calculation when the data data is stored in a single dimension.

For a moment I thought I could create a calculation that said if year = 2018, then give me the sales and a second calc that said if the year = 2017... Then in a third calc, figure out the % variance based on the two previously created measures. But I don't want to hard code the years, so that is a hurdle I need to jump. Our Financial Years also don't follow calendar years, with the year running from July to June, with KPI reporting produced one month behind (after sales are finalized), so I can't just look at today's date and compare it to last years.

Example below Cat 1 sales are 14% less than the year prior with \$76.6K less in sales.

Anyway I hope that all makes sense and someone is able to point me in the right direction.

Thank you for your time.

• ###### 1. Re: Variance % Against Last Year

You'd just need to quick table calc, and select YOY growth.

Will this work? If not, please mock up some data and attach a packaged workbook.

Best,

Shiva.

• ###### 2. Re: Variance % Against Last Year

Hello Loretta,

As Shiva already noted, it's probably easy enough to do in Tableau natively without need for special calculations. To meet your example, I provided this example below (Superstore Workbook 2018.2 attached) so you can show your fiscal year by setting the date properties to begin July 1 (or other period).  Just picking two Fiscal Years gets you a complete FY-2017 and YTD-FY-2018.  As new data arrives, the worksheet would update accordingly along with the variance.

Hope this helps with your concern.  Probably easier than you might think !

• ###### 3. Re: Variance % Against Last Year

Thank you for the help. I have gotten it working beautifully now.

• ###### 4. Re: Variance % Against Last Year

Good, please mark the responses as helpful and either as correct to close the thread.