From a story point of view, there's nothing wrong with a dystopian ending.
The way you describe it though, with success switched to defeat on the very verge of victory. with no warning whatsoever, is just cheap and nasty. You have to at least foreshadow it. Let us know early on that our chance of success is slim, that it is likely we won't return etc. People are so used to hearing that in successful adventures they wont think much of it at the time, but when you DO fail, it's harder to justify being angry at the game when it TOLD you near the start of the adventure how unlikely it would be that you'd pull it off.
And like others have said, if you lose, but cheat death and get to see the ruined world after the end of the story, then there's more sense of causality to it. Not to mention if the game has stated you were essentially going on a suicide mission, you can console yourself that while you lost your adventure, at least you survived despite titanic odds against you.