It is possible -- indeed, probably quite reasonable to see the infrastructure costs, etc, as investment rather than a drain. Perhaps even if it's done smartly many of the other necessary measures can be made to be, if not money-making, then at least not economically ruinous. But really you're trying to set up entire new industries, or accentuate existing ones at the expense of old ones, and I don't think anyone can pretend that this will go smoothly or be easy.
Not sure how to answer the Brexit comparison, except to point out that it's at best only tangentially related, and that if they are both damaging to the economy then it would be for vastly different reasons.
In pure climate terms, the target of net-zero Carbon emissions by 2050 is actually not nearly ambitious enough, and we should "really" be aiming for 2030. But that is impossible in practice, and it's better to be cautiously optimistic that this gets the UK moving in the right direction, and maybe prompting the rest of the world to follow. It may or may not surprise you to learn, v-e, that no matter how much I care about this subject, there are people who are even more passionate about it, and I've been somewhat frustrated by how dismissive they seem to be of this new measure.