Good afternoon,
We have been exploring ideas on how to accomplish identifying if a date is within one or more date ranges and are at a loss. We appreciate any and all of your help!
Overall goal: We have a singular date that we want to compare to a start and end date for each individual. Each individual could have 1+ start and end dates, which have been ranked based on the earliest date ranges. We would need the singular date to be compared to each ranked date range until it is within the date ranges or it is not, which would result in a "NO".
Example Data:
Continuing this example, below would be the results we would like from a measure (preferably) or calc. column (trying to minimize memory usage):
More data will continue to be added on an ongoing basis. We are looking for a sustainable and efficient way to include searching through all date ranks, as they increase over time.
Thanks again!
We have been exploring ideas on how to accomplish identifying if a date is within one or more date ranges and are at a loss. We appreciate any and all of your help!
Overall goal: We have a singular date that we want to compare to a start and end date for each individual. Each individual could have 1+ start and end dates, which have been ranked based on the earliest date ranges. We would need the singular date to be compared to each ranked date range until it is within the date ranges or it is not, which would result in a "NO".
Example Data:
| Individual | Date Start | Date End | Rank |
1 | 1/1/2019 | 6/30/2019 | 1 |
1 | 6/30/2019 | 12/31/2019 | 2 |
2 | 4/1/2018 | 10/31/2018 | 1 |
3 | 2/1/2019 | 8/31/2019 | 1 |
3 | 8/31/2019 | 3/30/2020 | 2 |
3 | 3/30/2020 | 10/30/2020 | 3 |
4 | 3/5/2019 | 10/5/2019 | 1 |
5 | 8/1/2019 | 3/2/2020 | 1 |
5 | 3/2/2020 | 10/2/2020 | 2 |
6 | 5/1/2019 | 12/1/2019 | 1 |
6 | 12/1/2017 | 7/1/2018 | 2 |
6 | 7/1/2018 | 1/31/2019 | 3 |
6 | 1/31/2019 | 8/30/2019 | 4 |
6 | 8/30/2019 | 3/30/2020 | 5 |
6 | 3/30/2020 | 10/30/2020 | 6 |
6 | 10/30/2020 | 5/30/2021 | 7 |
Continuing this example, below would be the results we would like from a measure (preferably) or calc. column (trying to minimize memory usage):
| Individual | Singular Date to Check | Within Date Start and Date End |
1 | 8/14/2019 | YES |
2 | 3/2/2019 | NO |
3 | 3/30/2019 | YES |
4 | 1/1/2020 | NO |
5 | 9/18/2020 | YES |
6 | 4/2/2018 | NO |
More data will continue to be added on an ongoing basis. We are looking for a sustainable and efficient way to include searching through all date ranks, as they increase over time.
Thanks again!
