3 Year Claims using Policy Level Context - Applying Context Transition

Hi Freece4802 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. 

Hi Freece4802 ,

I understand you're working on a complex time intelligence scenario with policy renewals and claims data. Based on your description, I can provide a solution, but to ensure it's perfectly tailored to your model structure, I'd like to clarify a few details:

Quick questions to optimize the solution:

1. Relationship structure: How are your Policy and Claims tables related? Is it a direct relationship via PolicyNumber, or do you have a bridge table?
2. Claims amount vs count: You mentioned "total number of Claims" - are you looking for a COUNT of claim records or SUM of claim amounts?
3. Date field in Claims: What's the name of your date field in the Claims table (ClaimDate, IncidentDate, etc.)?

Here's my initial approach based on your requirements:

3 Year Claims Count = SUMX(
    VALUES('Policy'[PolicyNumber]),
    VAR CurrentPolicy = 'Policy'[PolicyNumber]
    VAR RenewalDate =         CALCULATE(
            MAX('Policy'[RenewalDate]),
            'Policy'[PolicyNumber] = CurrentPolicy        )
    VAR ThreeYearStart = DATEADD(RenewalDate, -3, YEAR)
    
    RETURN
    CALCULATE(
        COUNTROWS('Claims'),
        'Claims'[PolicyNumber] = CurrentPolicy,
        'Claims'[ClaimDate] >= ThreeYearStart,
        'Claims'[ClaimDate] < RenewalDate    )
)

This handles your star schema structure and works at both day and month levels. However, if you can confirm those relationship details, I can refine this to be more precise for your specific model.

Would you mind sharing those clarifications so I can provide the most accurate solution?

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

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.

Hi Freece4802,

If the issue still persists, we kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar

Hi Freece4802,

we kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar

Hi Freece4802,

kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar