4 Replies Latest reply on Mar 20, 2014 12:13 PM by Ivan Monnier

# Tracking previous YTD quantities

Greetings!

I am a new user to Tableau, and I'm trying to calculate a % difference between Current YTD volume and Previous YTD volume.

I can calculate current YTD easy enough, however I'm having difficulty arriving at the correct number for the Previous YTD figures.

I'm trying to use a calculated field, and have used the following formula:

IF

DATEDIFF('year',[Order Date],TODAY())=1

AND

Month([Order Date])<=MONTH(TODAY())

THEN

[SALES]

END

This returns a result that is correct if calculating for the first two months of the year, but I don't want the remaining days of February to be calculated. I want a true Year to date.

Can someone assist on how to write the proper formula to accomplish this?

For example, I want all sales figures from 01/01/12 through the 02/04/12 (assuming today is 02/04/13.

Any help is greatly appreciated.

John

• ###### 1. Re: Tracking previous YTD quantities

Does this work for you?

IF DATEPART('month',TODAY()) <= 2 THEN

IF DATEDIFF('year',[Order_date],TODAY())=1 AND

DATE([Order_date]) <= TODAY()-366

THEN [SALES] END

ELSE

IF DATEDIFF('year',[Order_date],TODAY())=1 AND

DATE([Order_date]) <= TODAY()-365

THEN [SALES] END

END

The date difference with the same date last year, 2013 vs 2012, is 365 for March-December and 366 for January and February. ( If you would want the exact same (week)day last year use -364 )  There might very well be a more elegant solution that works for next year as well though....

• ###### 2. Re: Tracking previous YTD quantities

YES! Thank you very much!

• ###### 3. Re: Tracking previous YTD quantities

Here's a formula that uses the calendar, so Feb 29th in a leap year returns all of the prior February, and February 28th in a post-leap year returns all of Feb up to the 28th, while March 1 in a non-leap year returns all of February.

IF MONTH ([Order Date]) <= MONTH(TODAY()) AND DAY([Order Date]) <= DAY(TODAY()) THEN

IF YEAR([Order Date]) == YEAR(TODAY()) THEN

"YTD"

ELSEIF YEAR([Order Date]) == YEAR(TODAY()) -1 THEN

"Prior YTD"

END

END

1 of 1 people found this helpful
• ###### 4. Re: Tracking previous YTD quantities

Hello,

what would be the formula with quarters ?

Thank you