11 Replies Latest reply on Jan 10, 2020 10:40 AM by Antony Wambui

# Current YTD vs Previous YTD

Hello All,

I hope this message finds you well.

On the attached, I have revenue for 12 periods in 2016 and 5 periods in 2017. I would like to create a calculation that would compare revenue for the first 5 periods in 2017 vs the first 5 periods in 2016. I would like the calculation to show the \$ increase/decrease and the percentage increase/decrease. Also, this calculation needs to be dynamic so when new periods are added in 2017, the calculation would change and add this period.

Mikey

• ###### 1. Re: Current YTD vs Previous YTD

This is such a good question, Mikey.

First, you'll need to create an equation that separates the Fiscal Periods by the last three digits. This will allow us to tell Tableau what to refer to when we are doing our math. It will look like this:

Next, drag that to the right of the [Fiscal Year Periods] field on the Rows shelf. Then, right-click the pill and uncheck "Show Header".

Next, we'll create a Table Calculation to create the YoY \$ Difference value. On the [Revenue] field that is already out there, right click it, hover over "Table Calculation" and select "Difference". Then, right click that same pill, click "Edit Table Calculation", and then make the Table Calc screen look like this:

This will give you your YoY \$ Difference value.

Do the same steps above to create the YoY% Difference but instead of selecting "Difference", select "Percent Difference". The same steps should follow.

After that, you should be able to add Revenue back to the view and have all three metrics displaying like this:

Hope this helped!

1 of 1 people found this helpful
• ###### 2. Re: Current YTD vs Previous YTD

do we need to drag revenue field twice on the row shelf ?

• ###### 3. Re: Current YTD vs Previous YTD

This is what the set up looks like:

• ###### 4. Re: Current YTD vs Previous YTD

Here's a slightly different approach. Requires a few more calculations but allows you do some different things with your views.  There are 4 main calculations:

1. Date Conversion which makes a date out of your Fiscal Year Period

Date(Right([Fiscal Year Periods],2)+'/1/'+left([Fiscal Year Periods],4))

2. Last Period Calculation called Last Period:

{MAX([Date Conversion])}

3. FYTD Flag

DATEDIFF('year',[Date Conversion],[Last Period])=0

4.  PY Flag

DATEDIFF('year',[Date Conversion],[Last Period])=1
AND
DATEPART('month',[Date Conversion])<=DATEPART('month',[Last Period])

From there, we can build FYTD Revenue and Prior Year Revenue metrics which can be used in a summary.  See the attached for details.

Thanks!

1 of 1 people found this helpful
• ###### 5. Re: Current YTD vs Previous YTD

Keith,

This is EXACTLY what I'm looking for...thank you so much for all your help!

I do have one remaining problem -> just yesterday my IT department changed my date format in my dataset. I now have a DATE field called "Revenue Date", which is nice since I will not have to compute a date conversion anymore; however, this has caused issues when I tried ti implement your solution. On the attached, I try to mirror your formulas; however, the new date field is causing me some issues. On the attached, instead of comparing the entire current year to date vs the previous year to date, for some reason my visual is only comparing Jan and Feb 2017 vs Jan and Feb 2016. Can you take another look?

Again, thank you so much for all your help!

Cheers,

Mikey

• ###### 6. Re: Current YTD vs Previous YTD

Hi Mikey,

In your original question, you were looking at fiscal periods. When does your fiscal calendar begin? For fiscal year to date calculations, I typically take the revenue date, create an adjusted revenue date that moves the date forward so that the fiscal start month = January.

For example, my company fiscal calendar begins in May. So my formula would look like:

I would also do the same thing for the Last Period

Then all the work you have done so far would then point to these date calculations for the FYTD flag and the PY Flag.

I've added a parameter to your workbook so you can see how this could all work. The final step, you will want to set the date properties on your 'Revenue Date' for the fiscal start month.  Two places that I know of that you need to do this.

1. Right click Revenue Date and choose Default Properies -> Fiscal Year Start -> Choose the appropriate month

2. Right click the data source icon and choose Date Properties. Select the appropriate fiscal starting month.

Attached is your workbook which you can look how I formatted your Revenue Date for a fiscal start month of October.

• ###### 7. Re: Current YTD vs Previous YTD

thank you so much, Keith for your detailed explanation and time!

• ###### 8. Re: Current YTD vs Previous YTD

Very Informative Keith! Thank you.

Going one step further, how would you do this for just the current month vs same month previous year? Could you do a toggle between the two i.e. Toggle between YTD comparison and Month comparison.

• ###### 9. Re: Current YTD vs Previous YTD

HI Shruti,

The process is similar with the date flags I described earlier in the thread. You need to create a series of date filters, I always call them flags as I want them to be either True or False.

Current Month Flag:

Datediff('month',{max(Calendar Date)}) = 0

Same Month Last Year Flag:

DATEDIFF('month',[Calendar Date], {max(Calendar Date)})=12

and

DATEPART('day',[Calendar Date])<=DATEPART('day' , {max(Calendar Date)}) ***

*** The And portion of the formula is optional. It is a way to ensure you are not comparing a partial month to a full month.

To keep the two periods together, I create a third flag.

Month over Month Flag:

[Current Month Flag] or [Same Month Last Year Flag]

To swap select the period you want to display, one method might be to create 2 choice parameter for FYTD and Month over Month. What makes it all work is a calculated field that would switch between the two period filters. In my attached example, the field is called Period Filter

Period Filter:

If [Period Selection] = 1 THEN [Fiscal YOY Flag] ELSE [Month over Month] END

I hope this helps!

Keith

1 of 1 people found this helpful
• ###### 10. Re: Current YTD vs Previous YTD

Brilliant! Thanks Keith

• ###### 11. Re: Current YTD vs Previous YTD

Hi Keith,

Thank you for your contribution and insight in this thread. However, what would happen in a situation where you have several years, not just two like in Mikey's case, and you want to show percent difference for all the years with the full 12 months but also making sure that the last two years are comparing on year to date bases like you have explained so that prior to the last(max) year having the full 12 months of data, you can be comparing on YTD basis with the previous year and show all this in the same visualization?

Thanks!