I'm have trouble with my formula to calculate a variance to prior year end.
I have three different formula:
Auto 2011 YE = if [Termination Date] = DATE(12/2/2011) then [LEW %] end
Auto Current Month = if [Termination Date] = DATE(7/1/2012) then [LEW %] end
Auto Var to 2011 YE = [Auto Current Month]-[Auto 2011 YE]
The first two I keep getting the error "cannot mix aggregate and non-aggregate comparisons or results in "if" statement".
Anyone have any idea how I can fix this?
Note: In my data source I have all the data from YE 2011 in as the date 12/2/2011 and the date for July (the current month I'm working with) as 7/1/2012
The reason you're getting the error is probably because LEW % has an aggregation on it.
By changing the calculation to be similar to the following:
if max([Termination Date] )= DATE(12/2/2011) then [LEW %] end
Hope this helps!