I played Bioshock Infinite for the first time last weekend, it got me addicted enough to play and finish the story all in one weekend.

Probably Super Mario 3D World.

I say probably because Bayonetta 2 comes damn close, the fact that it's made me a fan of a genre that I never liked, is really saying something. The one 'problem' I have is that you don't actually need to be that good to finish the game and it's not until you start getting good that you can really appreciate how meticulously crafted the combat system is, it is staggeringly deep. I suspect though that many never get to see that depth.

Bloodborne, but that's the second to last game I've played anyway, the last being Shovel Knight which is good but not great imo.

Everquest: Ragfire BETA

Currently playing with friends

was great in 1999

still great to this date.

latest: super metroid, super castlevania 4. super ghouls and ghosts.

Super Mario Galaxy. Haven't played Majora's Mask yet though

Bloodborne. The game is so good, it's ridiculous. I'm still not all way through though.

Metroid Prime 2: Echoes

I would say X:COM Enemy Within if it wasn't for the fact that the ending disappointed me so much.

My choice would have to be Splinter Cell: Blacklist. It is an exquisitely made stealth game that felt fresh, polished and filled with a lot of content. I would not use "masterpiece" to describe it since that for me is a 10/10, but I only have like two of those in my entire collection, so I had to leave that out.

I'd like to say The Evil Within, save for the DLC screwing the original plot of the game and some small questionable gaming design here and there. Still a masterpiece in my eyes.

But if I were to say a game that fits into OP's description, the last great, unforgettable game I played was Dragon's Dogma.