10 Replies Latest reply on Apr 11, 2017 6:26 AM by Matthew Risley

# YOY/YTD calculation Difference

Hello,

I am trying to calculate monthly YOY difference, using table calculation. Calculation works fine until previous month but the current month is misleading to to my users as it calculates only MTD of this year and all days of current month last year.

Is there a way to limit day date for last year to match this year and yet show monthly difference.

Example,

Today's date April 10 2017

Sales of April as of April 9 2017- \$100

Sales of April as of April 9 2016- \$150

Sales of April as of April 9 2016- \$1500

If I am showing monthly year over year difference, I need to have April as \$50 worse than last year rather than \$1400 worse than last year

Tableau 9.1 Version

Pj

• ###### 1. Re: YOY/YTD calculation Difference

Hey PJ,

I've encountered this many times before. How I've chosen to tackle this -

I calculate the MTD for this month -

if datepart('month',[Date]) = datepart('month',NOW())

AND datepart('year',[Date]) = datepart('year',NOW()) then [Sales] END as 'MTD Sales'

I calculate the MTD for last year -

if datepart('month',[Date]) = datepart('month',NOW())

AND datepart('year',[Date]) = (datepart('year',NOW()) -1)

AND datepart('day',[Date]) <= datepart('day',NOW())

then [Sales] END as 'LY MTD Sales'

And compare the 2 values.

Galen

1 of 1 people found this helpful
• ###### 2. Re: YOY/YTD calculation Difference

Hey PJ,

So you want MTD for every Year, is that correct?

Then this should do it (Just change ORDER_DATE to your date field):

(MONTH([Order Date]) < MONTH(NOW()) OR (MONTH([Order Date]) = MONTH(NOW()) AND DAY([Order Date])

<= DAY(Now()))) 1 of 1 people found this helpful
• ###### 3. Re: YOY/YTD calculation Difference

Pardon, Matt?

I was responding to PJ's question, my calculation is designed to compare MTD sales to previous year MTD sales.

• ###### 4. Re: YOY/YTD calculation Difference

Haha well that's embarrassing! I hit the wrong reply button AND used the wrong name!

My apologies!

• ###### 5. Re: YOY/YTD calculation Difference

Month([Order Date]) < Month(NOW()) OR Month([Order Date]) = Month(NOW())

you can use the <= operator (less than or equal to)

Month([Order Date]) <= Month(NOW())

1 of 1 people found this helpful
• ###### 6. Re: YOY/YTD calculation Difference

Hi,

Some how this did not give correct results

• ###### 7. Re: YOY/YTD calculation Difference

Hey Galen,

Thanks a lot, that helped.

• ###### 8. Re: YOY/YTD calculation Difference

Hello Matthew,

Thanks a ton! This is exactly what I was looking for.

Pj

• ###### 9. Re: YOY/YTD calculation Difference

Sorry guys, I somehow don't have "Correct answer" option. (I had this issue earlier too but I thought I marked it as discussion. Now made sure its a question but it is still not there)

Well anyway that saves me the confusion to choose between which one to mark as both of your suggestions helped. (Assuming I can mark only 1 as correct)

Thank you so much!

tableaunewbie

Pj

• ###### 10. Re: YOY/YTD calculation Difference

This actually will not produce the same result. Using the OR operator insures that it only applies the "Day level" filter to the current month. If you use the <= Operator you will filter every month up to the Current Day of NOW(). I have no idea why, but look at example below: vs 