8 Replies Latest reply on Feb 22, 2017 1:24 PM by Shinichiro Murakami

# How to calculate current year and last year values for a measure.

Hi,

I am a beginner and am trying to learn the basics on Tableau.

I have a requirement where i have a measure "Sales" and Dimension "Year",

I need to calculate "Chg Sales" column to be displayed in a table.

calculation for "Chg Sales" = ( Current Year Sales - Prior Year Sales)

Year values

2014

2015

2016

User filters on year, Only year 2015 and 2016 are visible for selection. Always the current year and prior year are only displayed for filter selection by users.

If user select 2016 "Chg Sales" = ( 2016 Sales - 2015 Sales)

If user selects 2015 " Chg Sales" = (2015 Sales- 2014 sales)

I need to calculate this dynamically as the year will change.

Thanks,

kim.

• ###### 1. Re: How to calculate current year and last year values for a measure.

Hi Kim - I answered this question a couple of weeks ago - his issue was he wanted to do week over week or month over month - the  principals are the same except you will use year as the parameter

Hi

I have attached a t10.1 workbook that goes at this from a different approach

See the image below

The approach is as follows:

1. Duplicate the data source
2. Link the two together on Date (note I just changed the name fields to This Period and Prior Period to avoid confusion
3. Create a Parameters - End Period Date the dates parameters are linked to the This period date dimension and the prior period dimension Note iI only pull the year off the date
1. This way your user can put in any date and get a comparison

1. For each measure you will need a calculated field that looks something like below

You should end up with something like below

Let me know if this helped you out

Jim

• ###### 2. Re: How to calculate current year and last year values for a measure.

Hi, Kim

Jim's answer is good, but if you don't like parameter which does not allow dynamic year changes, here is another approach.

But not 100% met your request.

[Sales This year]

(sum([Sales]))

[Sales Last year]

lookup(sum([Sales]),-1)

[Sales Change]

[Sales This year]-[Sales Last year]

[Year Filter]  // I cannot hide "null", then I change the null value to the text " Select year from below"

if attr(year([Order Date]))<>attr(({fixed:max(year([Order Date]))}-2))

then  str( lookup(min(year([Order Date])),0))

else " Select year from below" end

Thanks,

Shin

• ###### 3. Re: How to calculate current year and last year values for a measure.

Yes Shin you are correct - when I wrote the original solution the user wanted to be able to look at month/month yr/yr or wk/wk off a single viz- the parameters let him chose between time period and start and finish dates

Jim

• ###### 4. Re: How to calculate current year and last year values for a measure.

Jim

i understand. thank you for the explanation.

Thanks,

Shin

• ###### 5. Re: How to calculate current year and last year values for a measure.

Jim,

Thank You, for the input.

• ###### 6. Re: How to calculate current year and last year values for a measure.

You are welcome

Have a good day

Jim

• ###### 7. Re: How to calculate current year and last year values for a measure.

Shin,

I implemented your steps suggested. I do not what to see the Year column my table should only have.  Category, Subcategory, total Sales and then the Chg Sale. Only these 4 columns. When I delete the year column the filter values change and i can only select " select year from Below ( which is 2014 data) 2015 and 2016 don't show in the filter values.

Am i missing something. I am few days old on Tableau so please excuse my level of questions.

Thanks,

Kanti.

• ###### 8. Re: How to calculate current year and last year values for a measure.

Hi Kim

If you  don't mind, the fastest way is that you provide the packaged workbook with failure?

Thanks,

Shin