Forum Discussion
DAX query EARLIER function work for date range
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.
Hi Anonymous ,
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.
1 Reply
Hi Anonymous ,
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.