Comparing Grouped Pivot Table Data

[cwhaley1]

Hi all, as you'll see from the attached picture I have two PivotTables, both show the details of engineers.

These PivotTables link respectively to two difference reports -- last week's report and this week's report. The only information I'm concerned with in these reports is the engineer's contract end date. A comparison of the two tables will show me if that end date has changed since last week's report.

I need the PivotTable to show only the latest date recorded against an individual engineer's name. As you'll see, an engineer may have more than one contract end date. but for my purposes I only want to be able to see the latest date entered for that contractor alongside their name.

I've tried XLOOKUP and also altering settings in PivotTable (using the MAX formatting) but nothing has so far given me what I need. Does anybody know a solution for this?

 

[cwhaley1]

Does anybody have any ideas? Still struggling to find a solution.

Best I've found so far is to put the reports side by side and the apply conditional formatting to the date column, but as there are often multiple dates for an individual (the number of which change per report) it's no good for a line-by-line comparison.

Need to see the latest date from a series of dates per individual.