KNOWLEDGE BASE

## How To Calculate The Year-Over-Year Ratio That Has The Same Ending Date

Published: 02 Jun 2023

### Question

How to calculate the year-over-year ratio that has the same ending date?

For example, the latest date of the latest year is 2023/5/30, so the the year-over-year ratio for the latest year should compare with the data from 2022/1/1 to 2022/5/30.

### Environment

• Tableau Desktop

1. Create the following calculations:
• latest month of the latest year
```{fixed: max(
if DATEPART('year', [Order Date])={fixed: max(DATEPART('year', [Order Date]))}
then DATEPART('month', [Order Date])
END)}```
• latest day of the latest year
```{fixed: max(
if DATEPART('year', [Order Date])={fixed: max(DATEPART('year', [Order Date]))}
and DATEPART('month', [Order Date])=[latest month of the latest year]
then DATEPART('day', [Order Date])
END)}```
• Sales until latest day
```if DATEPART('month', [Order Date])=[latest month of the latest year]
then if DATEPART('day', [Order Date])<=[latest day of the latest year]
then [Sales] END
elseif DATEPART('month', [Order Date])<[latest month of the latest year]
then [Sales]
END```
• Year On Year Ratio
```if attr(DATEPART('year', [Order Date]))=attr({fixed: max(DATEPART('year', [Order Date]))})
then ZN(SUM([Sales until latest day]))/LOOKUP(ZN(SUM([Sales until latest day])),-1)
else ZN(SUM([Sales]))/LOOKUP(ZN(SUM([Sales])),-1)
END```

2. Add Year On Year Ratio to Text, right click the data field select Compute Using...

3. Computing using Year of Order Date.