Portal, CO4, etc. all go without saying, But one of my favs is when you complete a campaign on M2:TW. Although it doesn't end any sort of storyline, it is really powerful, seeing your king being crowned by the Pope as the ruler of your nation's empire, with some booming narrator's voice, flags and trumpets of your nation everywhere, and hordes of people outside the castle cheering as well as fireworks. The picture of the king and his wife then fades to a book drawing, which then closes and some monk guy puts it on the shelf, finishing your nation's greatest period in history.

And I can't be bothered to think of anymore right now.