If you provide the statements to create the test data, it will be a lot easier to give you some help.
My guess is you need to join on a sub-select of the table with the max(snapshot_date) < requested date.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **