Leo continues to collect powers and be promoted. Give this guy another couple seasons, and he would be God.
Season 6 kind of kicked into gear with a vision quest by Phoebe, and Season 7, or at least the cumbersome Avatar arc, kicks into gear with a vision quest for Leo, in which the Avatars reveal themselves as this "gathering storm" that ends up being a little more like a mild dusting.
Not sharing my opinion on the grand event, a demon named Sarpedon decides to gather guardian angels to protect himself. Questionable plan, but who needs the Source, the Triad, Zankou or anyone else when you have this guy? The guy manages to get Paige's guardian, slaps Brody around a little, and then kills Piper and Phoebe, single-handedly, though he himself is killed in the process.
Of course, Piper and Phoebe dying is nothing to worry about as long as Leo is around. Leo snaps out of his chit-chat with the Avatars just in time to find Piper and Phoebe dead. He seemingly can't heal them, too late, though that seems questionable considering the multiple stages of death, near death, and everything else that Leo has brought them back from. Anyway, the Avatars make him an offer he can't refuse, join them or let Piper and Phoebe die. Not much of a choice really. And thus, Leo becomes an Avatar, heals the sisters, and at least we have some answers and an end to the "Leo being crazy" story arc.