I have the following calculation (also see attached) which is giving me a Matched Cohort of students (a group of students who were present in all of the previous three years).

{FIXED [State ID]: MAX(IIF([Year]=[Current Year] AND [Grade] = [Current Grade],1,0))}=1

AND

{FIXED [State ID]: MAX(IIF([Year]=[Current Year]-1 AND [Grade] = [Current Grade]-1,1,0))}=1

AND

{FIXED [State ID]: MAX(IIF([Year]=[Current Year]-2 AND [Grade] = [Current Grade]-2,1,0))}=1

Current Year and Current Grade are parameters. The formula works but I have the following problem: When I'm looking at a 3rd grade student there are not two previous years worth of data (we don't start testing until 2nd grade). So the display shows up blank when Current Grade is set to 3.

I'd like to build in some logic that says, If the user chooses Grade 3 then only show one previous year. If they select Grade 2 then only show the current year with no previous years. If they select any other grade then go ahead and show all three years.

However, when I try to build in an If statement I get the error that If statements can't be used with Boolean data.

Is there a way to get around this? Or a simpler approach to the problem that I'm not thinking of?

(All of the data are protected so I hope we can work this out without an attached packaged workbook - if not, I can try to anonymize it somehow. Thanks.)