Hi Mynda and team!

I've been working with Power Query within the last couple of months, and now I've run across having to figure out custom column formulas using this language.  I've reviewed the blog on conditional formulas for nested ifs, which is very helpful. But not sure how to manage what I need for this, which is something I need to calculate often.

What I'm trying to do is replicate a change or variance column based on values from one time frame to another.  In this case, I have year-over-year columns for prior year and current year.

Typically, I would write the formula in Excel or a similar calculated pivot column that would translate to something like this:

First if condition: if(Cases_PY = 0 then

2nd if condition (true part): if(Cases_CY >0 then 1 else 0)

back to 1st condition (false part): (Cases_CY-Cases_PY)/Cases_PY

Otherwise 0

So, this says that if prior year cases are 0 then validate if current year cases are >0.  If so, then provide "1" (or 100%).  If not, then the 2nd condition will result in 0.

But if 1st condition is not 0, then do the formula.  This is to avoid the divisor !=0 error.

Is there a way to translate this to a column in power query?  I have a few of them to write that will match this logic.  

Also, is it necessary to setup a Difference column for the current and prior years values instead of doing it in the formula?

** Additional question: I meant to ask if I were to setup the calculated field in PowerPivot if it would be dynamic in Power View reports/charts?  Will it update based on the slicers and filters?

Thanks for your assistance!

Robin