As is the case with so many things in games, it's all in the implementation.
My personal favorite example of weather affecting gameplay on the tactical level is (no surprise) the Total War series. Unfortunately, I can't really think of a game I've played where weather mattered at the strategic/operational level, at least not done well.