Forum Discussion

Freece4802's avatar
Freece4802
Frequent Visitor
1 year ago
Solved

3 Year Claims using Policy Level Context - Applying Context Transition

Hi all,    So I'm looking to create a Measure which calculates the total number of Claims by Policy 3 years prior to the renewal date of a Policy. For example:   Policy due 15/01/25 would have a ...
Need Help
  • Freece4802's avatar
    Freece4802
    1 year ago

    Hi all, 

     

    Apologies for the slow reply on this. I've come up with a working solution, specifically, creating a dummy column linking to a policy term and then creating a bi-directional relationship to establish a max date relative to the dimension. 

     

    I've provided the code below for those who may find this useful. 

     

    VAR MaxDate =
        MAX ( 'Date Table'[Date] )
     
    VAR ClaimsTable =
        ADDCOLUMNS (
             CALCULATETABLE(VALUES ( Claims[PolicyNumber] ), REMOVEFILTERS('Date Table')),
            "GWP",
                VAR RenewalDate =
    -- Bidirectional relationship to Dimension table to establish the max date relative to date above
                    CALCULATE (
                        MAX ( 'Policy Term'[PolicyTermStartDate] ),
                        'Policy Term'[PolicyTermStartDate] <= MaxDate,
                        CROSSFILTER ( 'Policy Term'[PolicyNumber], Claims[PolicyNumber], BOTH ),
                        USERELATIONSHIP ( 'Policy Term'[PolicyNumber], Claims[PolicyNumber] ), --Default relationship is usually the term ID
                        REMOVEFILTERS('Date Table')
                    )
                VAR ThreeYearStart =
                    EDATE ( RenewalDate, -36 )
               
                VAR CutOff =
                   EDATE ( RenewalDate, -3 )
                RETURN
                    CALCULATE (
                        SUM ( Claims[GrossIncurred] ),
                            Claims[EventDate] <= CutOff && Claims[EventDate] >= ThreeYearStart,
                            Claims[TransactionDate] <= CutOff,
                        REMOVEFILTERS ( 'Date Table' )
                    )
        )
    RETURN
        SUMX ( ClaimsTable, [GWP] )


    Any feedback on optimisation would be appriciated though!

     

    Thanks, 

    Will 

burakkaragoz's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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.

Freece4802's avatar
Freece4802
Frequent Visitor
1 year ago

Hi burakkaragoz, See below for comments on your questions:

  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? -Claims are Policies are connected by Common Dimensions and not a bridge table. So Dimension PolicyNuimber is present in both fact tables. Awakwardly enough, the start date can be obtained either by the Fact Policy Table, or a seperate dimension called Policy Term which relates to each term of a policy. 
  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? - Both have their value by initially just COUNT
  3. Date field in Claims: What's the name of your date field in the Claims table (ClaimDate, IncidentDate, etc.)? Incident date