Most of the criticism of Yzerman is pretty bad faith, and that's coming from a Sens fan who has no love for the Wings.
He took over a team with arguably the worst future of any team in the league. A couple decades of contending and god awful drafting in the 2010s left the Wings with no young stars in the organization beyond Larkin.
It can't be overstated that when Yzerman took over the best young players on the roster were Larkin, Hronek, Bertuzzi and Mantha, and the Wings had just spent their previous 4 1st round picks on Zadina, Veleno, Rasmussen, Cholowski and Svechnikov, with their day 2 drafting being no better.
It shouldn't shock anyone that the Wings haven't made the playoffs yet during his tenure. Would have taken any GM at least half a decade to right the ship.
Where I think he messed up is rushing the rebuild a bit by adding talent in UFA too early. Wings need a few more young stars but their team ended up winning too many games, resulting in their last 3 1sts ending up being 8th (Kasper), 9th (Danielson) and 15th (Brandsegg-Nygard), where it's difficult to find elite talent. I'm also not a huge fan of the Kasper/Danielson picks for them. Both will end up good NHLers, but thought it would have been smarter for them to take swings at guys with higher upside, given their terrible lottery luck (which isn't Yzerman's fault) didn't gift them any elite talent.
Not sure where the Wings go from here. Have the makings of a consistent playoff team once all the young players develop, but it's hard to imagine them taking the next step into contending status.