Count Expression not showing appropriate output?

[wholelottared]

Hello all,

I'm using an expression to count contracts

=count([Master Contract Number]) which shows a value over 10000 (Correct)

BUT when I use the same expression format but before (Number of contracts expiring on or before 05/20/20)

 =count(distinct(if([Ultimate Completion Date]<='05/20/20',SUM,0))) shows only the value of 2 (there are many more than 2)

Any help is appreciated

more complicated expression shows a value of 0

=count(distinct([Master Contract Number])/count(distinct(if([Ultimate Completion Date]<='9/30/20',[D2D Match],0)) it doesn't work either.

[Vegar · Accepted solution]

Try transforming your expression into an set analysis and make sure your string date, '05/20/20', is interpreted as a date

Maybe like this. 

Count({< [Ultimate Completion Date] ={"<=$(=date#('05/20/20','MM/DD/YY') )" } >} DISTINCT SUM)