3 Replies Latest reply on Jul 6, 2016 9:42 AM by Aiswarya Sundaram

Same time last year reference line

Matthew Hefferon Jul 6, 2016 9:26 AM

Hey Tableau Community!

I was hoping one of you can help me out with a calculation?

I have a bar chart showing the sum of sales for July 2015 and July 2016. We have a full bar of sales for the month of July 2015 but since we're only 6 days into July 2016 we have a short bar. I want to show the full month of sales so we can see where we need to get too but I also want to add a red reference line showing where we were the same time last year. Any thoughts or ideas appreciated!

Attached is a dummy workbook!

Thanks

-Matthew

Dummy Bar Chart.twbx 28.3 KB

1. Re: Same time last year reference line

Shinichiro Murakami Jul 6, 2016 9:39 AM (in response to Matthew Hefferon)
Does this work?

[Last Year to Date]
if year([Date])+1 = year({fixed : max([Date])} )
and [Date] <= dateadd('year',-1,today()) then [Net Sales] end

Thanks,
Shin

Dummy Bar Chart 9.3.twbx 48.4 KB

2 of 2 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Same time last year reference line

Aiswarya Sundaram Jul 6, 2016 9:42 AM (in response to Matthew Hefferon)

This calculation should help you.

if DAY([Invoice Date])<=DAY(TODAY()) and MONTH([Invoice Date])=MONTH(TODAY()) and YEAR([Invoice Date])=YEAR(TODAY())-1
then [Net Sales]
END

I have assumed your data set is on daily basis and that you are looking at Jul until 6th of last year.
Tweak it a bit to match your needs otherwise

Then create a reference line with this field with per pane scope.
Like Show 1 Likes(1)

Actions
3. Re: Same time last year reference line

Matthew Hefferon Jul 6, 2016 10:14 AM (in response to Matthew Hefferon)

Sounds good! Thanks for the help everybody!
Like Show 1 Likes(1)

Actions

Go to original post