Hello! I have tried so many combinations of ALLEXCEPT, FILTER, and CALCULATE to try to get a minimum date returned for a client based on several criteria. The difficult thing about the data is that it's messy and there are a lot of records that overlap and don't make a lot of sense, likely due to bad recordkeeping. Below is the actual data I am working with and some cases that are causing me the most trouble, especially #3. The far right column in the table copied below is what I ultimately need to return and use in calculations to get the average time from refer date to enroll date. I need to find the closest referral date after the entry date, then the closest enroll date after that refer date, like a sequence of events. Although client #1 here has two entry dates and two refer dates, only one record really falls into a logical sequence as the refer date is closest to the entry date and the enroll date is then closest to that refer date.
When I try to use FILTER (see example below) after an ALLEXCEPT statement, the results that get returned start to get very messed up. For client #3, when I try to use a FILTER to only select the closest enroll date where the enroll date is after the referral date, the results for some records are correct and for others are not. I can't seem to find a calculation that returns the correct results for all combinations of records. I know there is something about context here that I'm just not understanding, but the filter statement is just not working correctly and I can't figure out how to fix it.
ClosestTimeToEnroll = CALCULATE(MIN('dw CEPathway'[TimeReferToEnroll]),
ALLEXCEPT('dw CEPathway', 'dw CEPathway'[ID], 'dw CEPathway'[EnrollDate]),
FILTER('dw CEPathway', 'dw CEPathway'[EnrollDate] >= 'dw CEPathway'[ReferDate])
| ID | EntryDate | TimeToRefer | ReferDate | TimeToEnroll | EnrollDate | ClosestTimeToEnroll |
| 1 | 8/24/2017 | 19 | 9/12/2017 | 7 | 9/19/2017 | |
| 1 | 8/24/2017 | 1678 | 3/29/2022 | -1652 | 9/19/2017 | |
| 1 | 9/1/2017 | 11 | 9/12/2017 | 7 | 9/19/2017 | 7 |
| 1 | 9/1/2017 | 1670 | 3/29/2022 | -1652 | 9/19/2017 | |
| 2 | 2/9/2018 | 4 | 2/13/2018 | 924 | 8/25/2020 | |
| 2 | 5/4/2020 | 0 | 5/4/2020 | 113 | 8/25/2020 | 113 |
| 3 | 1/22/2019 | 7 | 1/29/2019 | 569 | 8/20/2020 | |
| 3 | 1/22/2019 | 7 | 1/29/2019 | 605 | 9/25/2020 | |
| 3 | 1/22/2019 | 569 | 8/13/2020 | 7 | 8/20/2020 | |
| 3 | 1/22/2019 | 569 | 8/13/2020 | 43 | 9/25/2020 | |
| 3 | 8/22/2019 | 357 | 8/13/2020 | 7 | 8/20/2020 | |
| 3 | 8/22/2019 | 357 | 8/13/2020 | 43 | 9/25/2020 | |
| 3 | 8/22/2019 | 400 | 9/25/2020 | -36 | 8/20/2020 | |
| 3 | 8/22/2019 | 400 | 9/25/2020 | 0 | 9/25/2020 | |
| 3 | 8/23/2019 | 356 | 8/13/2020 | 7 | 8/20/2020 | |
| 3 | 8/23/2019 | 356 | 8/13/2020 | 43 | 9/25/2020 | |
| 3 | 8/23/2019 | 399 | 9/25/2020 | -36 | 8/20/2020 | |
| 3 | 8/23/2019 | 399 | 9/25/2020 | 0 | 9/25/2020 | |
| 3 | 4/14/2020 | 121 | 8/13/2020 | 7 | 8/20/2020 | 7 |
| 3 | 4/14/2020 | 121 | 8/13/2020 | 43 | 9/25/2020 | |
| 3 | 4/14/2020 | 164 | 9/25/2020 | -36 | 8/20/2020 | |
| 3 | 4/14/2020 | 164 | 9/25/2020 | 0 | 9/25/2020 | 0 |
| 4 | 10/14/2021 | 1 | 10/15/2021 | 119 | 2/11/2022 | |
| 4 | 10/14/2021 | 1 | 10/15/2021 | 430 | 12/19/2022 | |
| 4 | 10/15/2021 | 0 | 10/15/2021 | 119 | 2/11/2022 | 119 |
| 4 | 10/15/2021 | 0 | 10/15/2021 | 430 | 12/19/2022 | |
