I have watched compelling visions, pitched by charismatic leaders, nonetheless fail to motivate. I think I've figured out why.

I agree that vision — a compelling description of a future state — is an important, necessary part of motivating a team. The vision must be something the team actually wants to achieve. But that alone isn’t motivating.

What bona fide motivation demands, in addition to an inspiring vision, is a strategy for how the vision will be achieved. Before a person is motivated, they must conclude, in their heart of hearts, that this strategy can succeed. The strategy must be genuinely credible.

What makes this difficult is that it's impossible to counterfeit. You can force people to profess their belief, but if they don't privately think the strategy can work, you'll experience friction the whole time. Conversely, if they do think the strategy will work, no obstacle will feel insurmountable.

Execution follows strategy, and I don't have anything to say about that except: most of our day-to-day attention goes into execution, and there's a world of difference between work in service of a strategy you believe in and one you don't. It's the difference between playing-to-win and playing-not-to-lose.

So here's the recipe for a motivated team:

Describe what you want (a compelling vision).
Describe a plan to go from here to there (a credible strategy). The important thing is that each person, privately, in their heart of hearts, believes this will work.
Follow this plan (competent execution)

When, inevitably, the plan doesn't survive contact with reality, adjust as needed based on what you've learned. If you did step 2 well enough, the team's belief in the plan will be a renewable source of motivation powering virtually any execution work. The vision can inspire; the strategy is what motivates.