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

Published: 02 Jun 2023
Last Modified Date: 05 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

Answer

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.
User-added image