Hello,
I have a table of policies which have start dates and end dates. The goal of this dashboard is to show active policies at the date range that is filtered for. A policy is active for a certain date range if its start date occurs before the select month/year and ends after the selected month/year. Therefore the flag for active policies will change depending on which dates the user selects.
In addition, I have multiple policy snapshots for the same policy and only want to show one, which would be the one with the most recent start date. See example below (assuming march 2023 is the selected date range).
| policy number | effective date | end date | active |
| 1 | 3/4/2023 | 12/1/2023 | 1 |
| 1 | 3/8/2023 | 12/1/2023 | 1 |
| 1 | 2/1/2023 | 2/28/2023 | 0 |
| 1 | 5/1/2023 | 6/1/2023 | 0 |
In this case, I would only show the second row (with an effective date of 3/8/2023).
To do this, I created a measure flag that determines if a policy snapshot is active for the given time frame. Remember this could change. So if the user selects February 2023, only the 3rd row would be considered active and given a 1.
I tried the following:
CALCULATE(MAX(table1[EffectiveDate],ALLEXCEPT(policynumber),filter(table1,activeMeasure=1))
However this gave me the following:
| policy number | effective date | end date | active | Maximum effective date |
| 1 | 3/4/2023 | 12/1/2023 | 1 | 3/4/2023 |
| 1 | 3/8/2023 | 12/1/2023 | 1 | 3/8/2023 |
when what I want is this:
| policy number | effective date | end date | active | Maximum effective date |
| 1 | 3/4/2023 | 12/1/2023 | 1 | 3/8/2023 |
| 1 | 3/8/2023 | 12/1/2023 | 1 | 3/8/2023 |
I played around it with some more and fouund that when I remove the measure filter(table1,active=1) clause, I get the following:
| policy number | effective date | end date | active | Maximum effective date |
| 1 | 3/4/2023 | 12/1/2023 | 1 | 5/1/2023 |
| 1 | 3/8/2023 | 12/1/2023 | 1 | 5/1/2023 |
I have been playing around with it for a while and cannot figure out how to fix this. I tried creating virtual tables within the measure but it gives me the same result.
How can I get the max date for each unique policy for the date range selected?
