DAX query EARLIER function work for date range

[rajendrabhole]

I want to count number of records before date. For example,

Iteration.StartDate	Iteration.EndDate	NewStateWithStartDate.ChangedDate	Column
01-01-2021 00:00	18-07-2021 00:00	15-07-2021 12:46	44130
01-01-2021 00:00	18-07-2021 00:00	11-03-2021 21:18	44130
01-01-2021 00:00	18-07-2021 00:00	11-03-2021 21:18	44130
01-01-2021 00:00	18-07-2021 00:00	11-03-2021 21:18	44130

So in my Column there should be 4 rows if I put condition `Iteration.StartDate < NewStateWithStartDate.ChangedDate,
I written DAX function for calculating count of rows,

Column = SUMX( JoinTable, IF ( EARLIER (JoinTable[Iteration.StartDate]) <= JoinTable[NewStateWithStartDate.ChangedDate], 1, 0 ) )

I want range of StarDate and EndDate must be less than ChangedDate.

In my actual problem there are so many StartDate, EndDate and ChangedDate.

@developer 

[V-lianl-msft · Accepted solution]

Hi @rajendrabhole ,

Please try to create DAX like below:

Column 2 = COUNTROWS(FILTER('Table','Table'[Iteration.StartDate]<='Table'[NewStateWithStartDate.ChangedDate]&&'Table'[Iteration.EndDate]<='Table'[NewStateWithStartDate.ChangedDate]))

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