The last game I was hyped over was Viking: Battle for Asgard. I loved the trailers and I was obsessed with Vikings at the time. I got it, I played through it on the hardest difficulty and loved it.
After a while, I wanted to play it again but I felt bored doing so.
Before that, there was Oblivion, I waited 4 years to play that... I was a bit disappointed because there were too many empty spaces and it didn't really live up to Morrowind for me(only in the combat, where I prefer Oblivion's). Great game though.