Comparing Dates Measure

[Anonymous]

Hello,

I am looking to make a conditional measure that compares two date fields. The first compares if the date is less than Todays date AND if there is a blank date in the other column. I can have the return be either a numeric count or categorical. There are multiple lines for each order so would like to find the max date that meets this criteria for each order. 

Currently using: Past = CALCULATE(COUNT('TABLE'[OrderNum]),FILTER('Table','Table'[First Date] < TODAY() && ISBLANK(Table[Second Date])))

But this is returning no output. 

Therefore the output would be something like this: 

Order	First Date	Second Date	Output
1	1/1/2022	1/3/2022	0 or "Good"
2	5/1/2022	4/1/2022	0 or "Good"
3	6/7/2022		1 or "Past"
1	1/1/2022		1 or "Past"

Additionally I have the challenge of erronious dates in the first date column that I would like to filter out.

Thanks so much for the help!

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

I have created a simple smaple, please refer to it to see if it helps you.

Create a measure.

Measure = IF(MAX('Table'[First Date])<=TODAY()&&MAX('Table'[Second Date])=BLANK(),1,0)

Or I have modified the measure. Please refer to.

Currently using: Past = CALCULATE(COUNT('Table'[order]),FILTER(ALL('Table'),'Table'[First Date]<=TODAY()&&'Table'[Second Date]=BLANK()))

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.