Varying date periods and binary values in a pivot table

This forum has been tremendously informative and I wonder if the experts here can provide advice. I have a project that I try to represent in the attached sample.

There are customers at different companies asked to provide a feedback score on service provided over a period. However some do this for each quarter, some each half, some only annually, some both quarterly and annually, some don't bother, etc.

I'm trying to summarise in a table:

- Was a score received from a company (Y/N)? >>Should I do this with a calculated item?

- What was the most recent period when a score was received? E.g. if there are scores for Q1 2020, H1 2020 and FY 2020, FY 2020 would somehow need to be assessed the most recent (based on end date of that period).

- As a bonus, I'd like to be able to return in adjacent columns the names of customers who provided a score

Any suggestions massively appreciated.

This is messy, but it works with the help of some helper columns in the source data.

The first thing we need to do i figure out the end dates for the quarters, halves and FY. I assume the FY ends on October 31. So I set up a table in columns T-V to define these things.

Then I calculated the end date in the period in column F: =DATE([@Year],VLOOKUP([@Period],Table_Period,2,FALSE),VLOOKUP([@Period],Table_Period,3))

I also calculated the maximum date for the customer + client combination (Columns G and I). and checked tp see if a particular record contained the max date (Column J)

Then I noticed that there were duplicate records, so I flagged these (columns H and K) =MATCH([@[Composite 2]],[Composite 2],0)=ROW()-1 <- this formula is true only for the first instance of the composite subsequent occurrences are false). So I am only counting the first rating.

Finally, there were people who did not submit a rating, so Column L takes care of them.

I took the liberty of swapping the columns and rows so people go down and companies and dates go across.