I'm trying to calculate employee turnover where the numerator is annualized only for the current incomplete year.
The closest I have gotten is TER/HC as a calculated Field in a pivot table. This allows me the flexibility to drag and drop fields, pivot, etc and have this field calculate turnover no matter the slice. But the problem is if I am viewing this year over year and say for example we are three months into the current year, current year will appear artificially low because it is not an annual number. The solution is to annualize current year.
I thought I could use an if function in a calculated field like so: if(FYR=2014, TER/2*12,TER)/HC. But this didn't work instead I got the same result as TER/HC. In this case FYR is Fiscal Year and we assume that so far there are two months in current fiscal year. TER is number of people who terminated (left the company) and HC is headcount.
What am I doing wrong? Can an if function be used in a calculated field? Do I have to use PowerPivot instead?
Now that I figured out the first problem I have a couple new related problems that I need to solve to get to my complete solution:
A) Why am I getting a #NUM! error in my grand total whenever I filter on FYR (Fiscal Year - a numeric column). This is my measure formula: CALCULATE(if(Max(AttritionExample[FYR])=2014,sum(AttritionExample[TER])*12,sum(AttritionExample[TER]))/sum(AttritionExample[HC])).
TER and HC are numeric columns
B) I added a new column to my Excel file then clicked PowerPivot window. I see my field was added there. But when I go to click update all and refresh my power pivot table the new field does not show up right away, not until a long while after.