3 Year Claims using Policy Level Context - Applying Context Transition

Hi Freece4802 ,

Here’s a DAX measure pattern that will reliably count the total claims per Policy, looking back 3 years from the renewal date (works even if the Policy table isn’t in your visual):

Claims in Last 3 Years = 
VAR SelectedTerm = SELECTEDVALUE('Policy Term'[PolicyTermID])
VAR CurrentPolicyNumber =
    CALCULATE(
        MAX('Policy'[PolicyNumber]),
        'Policy Term'[PolicyTermID] = SelectedTerm
    )
VAR RenewalDate =
    CALCULATE(
        MAX('Policy'[RenewalDate]),
        'Policy Term'[PolicyTermID] = SelectedTerm &&
        'Policy'[PolicyNumber] = CurrentPolicyNumber
    )
VAR StartDate = EDATE(RenewalDate, -36) // Go back 3 years

RETURN
CALCULATE(
    COUNTROWS('Claims'),
    'Claims'[PolicyNumber] = CurrentPolicyNumber,
    TREATAS(
        DATESBETWEEN('Date'[Date], StartDate, RenewalDate),
        'Claims'[IncidentDate]
    )
)

This measure uses TREATAS to filter your Claims table by incident date, only counting claims within the right 3-year window for each policy’s renewal. The logic is flexible it’ll work at month or day level, and will respect whatever context you set in your visuals. If your claims table is linked via a different key or you only have PolicyTermID in your visuals, you might need to adjust the variable part, but this pattern should get you 95% of the way there. If you need a simplified version (say, you’re already visualizing by Policy Term and Renewal Date is in context), you can shorten it:

Example with TREATAS:

Claims in Last 3 Years =
VAR RenewalDate = MAX('Policy'[RenewalDate])
VAR StartDate = EDATE(RenewalDate, -36)
RETURN
CALCULATE(
    COUNTROWS('Claims'),
    TREATAS(
        DATESBETWEEN('Date'[Date], StartDate, RenewalDate),
        'Claims'[IncidentDate]
    )
)

This pattern will keep everything dynamic, so even if you slice or aggregate by month/year, you’ll always get the correct claim counts per policy’s 3-year lookback.