Ticket buyers who have bought in multiple years pivot table

Hello, 

I am trying to make a pivot table of customers who have bought tickets to productions in multiple years. The end result would look something like this:

  2023 2022 2021 2020
2023 1118 724 505 317
2022   2220 1005 670
2021     1395 1005
2020       1456

In this table we have:

- 1118 constituents bought tickets in 2023

- 724 of those also had tickets in 2022

- 505 of those also had tickets in 2021

- 317 of those also had tickets in 2020

and so on...

After much work, I was able to generate one row of this, but it needed a formula for each column and did not work for subsequent rows.  My formula is 4 variants of thiis

if (
([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND
([Total Seat Count], [Season Fiscal Current Year Offset1]) >0 AND
([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND
([Total Seat Count], [Season Fiscal Current Year Offset3]) >0, 1, null)

Is there an easier way to achieve this result? 

Thank you!

Parents Reply Children
No Data