The most recent game worth a look is Lamplight City, a steampunk mystery game where you play as a detective who hears the voice of his dead partner in his head. There are five main cases, and your performances in said cases affects what progress you can possibly make in later cases. The creator basically borrowed the investigation point and click formula and added some structure from L.A Noire, which results in a game where cases only end once you finger a perp. You can be wrong, though, and this has consequences.
The Blackwell series from Wadjet Eye is a bit rocky, but the five games form a really strong story and improve drastically every entry. They follow a spirit medium and her ghost partner as they help spirits move on to the afterlife, dealing with various foes. Super New York, especially the second and third games making reference to two major New York City celebrities that turns into a really crazy story about reality shifting.
They also recently released Unavowed, set in the same universe and following a team of magic police tracking down a demon. The cool thing is the use of RPG elements, so you actually get to pick your gender and backstory, plus can choose who to bring in the field, which completely changes puzzle solutions.
The Gabriel Knight series is a must, those I advise you bring a walkthrough because they’re sierra games and were made at a time where they designed a lot of point and clicks that punished you with nonsense. They’re not as bad about it, but SOME MOMENTS. GAH. Anyways, they follow Gabriel Knight, a man who owns a bookstore in New Orleans and discovers he comes from a line of magical detectives. The first game has an all star cast and lovely sprite graphics, while the two sequels tried experimenting with FMV and 3D to…underwhelming effect. However, I hear their stories are way stronger.
Kathy Rain is good if you want something with a weird twist to it. The guy behind it said he was inspired by Twin Peaks and it REALLY shows.
I’ve also heard great things about Contradiction, a recent FMV point and click mystery game. Worth looking up.