Tableau: How to calculate YoY retention (current year value divided by sum of previous year's values)

Question

As per the screenshot (below), I would like to calculate in Tableau the percentage of a current value (this year's "RETURNING" total) versus the sum of a previous year's totals (previous year's "NEW" + "RETURNING" total).

My thinking is that I could:

1. Add the "Total" of [DistinctPersons] for each [Level] (using a LOD, but this returns an "aggregate of aggregates" error); then …
2. For each "RETURNING" value in [New or Returning], divide the current year's "RETURNING" value by the previous year's calculated "Total" values.

I know this probably would involve some combination of LODs and window functions but, because the measure is already an aggregate result, it's a bit trickier than I thought.

Answer 1

If you remove Year from the columns shelf and New or Returning from the row shelf, you do the following:

(IF ATTR([Year]) = 2022 AND ATTR([New or Returning]) = 'RETURNING' THEN [DistinctPersons] END)
-
(IF ATTR([Year]) = 2021 THEN [DistinctPersons] END)

Since [DistinctPersons] is already aggregated, the ATTR() is required around the other fields.