iif statement returning the wrong results

Question

I am using these expressions to group my data in columns

=CountDistinct(iif((Fields!I_Sch_Close.Value >= DateSerial(Year(Now()), Month(Now()), "1")
AND Fields!I_Sch_Close.Value <= DateSerial(Year(Now()), 
Month(Now()), "1").AddMonths(1).AddDays(-1)), 1,0))
=CountDistinct(iif((Fields!I_Sch_Close.Value >= DateSerial(Year(Now()),
Month(Now()), "1").AddMonths(1) AND Fields!I_Sch_Close.Value <= 
DateSerial(Year(Now()), Month(Now()), "1").AddMonths(2).AddDays(-1)), 1,0))
=CountDistinct(iif((Fields!I_Sch_Close.Value >= DateSerial(Year(Now()),
Month(Now()), "1").AddMonths(2) AND Fields!I_Sch_Close.Value <= 
DateSerial(Year(Now()), Month(Now()), "1").AddMonths(3).AddDays(-1)), 1,0))
=CountDistinct(iif((Fields!I_Sch_Close.Value >= DateSerial(Year(Now()), 
Month(Now()), "1").AddMonths(3) AND Fields!I_Sch_Close.Value <= 
DateSerial(Year(Now()), Month(Now()), "1").AddMonths(4).AddDays(-1)), 1,0))

Here is the outcome

Here is the data returned from the query

As you can see the April and May fields are showing 1 result returned, when I believe it should show 0. The No COE(I_Sch_Close) Column should be 11 but reads 2 with the following expression:

=CountDistinct(iif(ISNOTHING(Fields!I_Sch_Close.Value),1,0))

What am I doing wrong?

Answer 1

Your IIf statement in the following expression for No COE returns 1 or 0

=CountDistinct(iif(ISNOTHING(Fields!I_Sch_Close.Value),1,0))

The distinct count of those can't be more than 2. Instead, you need to count occurrences where your expression is True. Try something like this:

=Count(IIf(<expression>, 1, Nothing))