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 

rohit1991's avatar
rohit1991
Super User
1 year ago

Hi Freece4802 ,

 

Based on everything you've shared including that the Renewal Date lives in the Fact Policy table, and Claims are linked via common dimensions like PolicyNumber and possibly PolicyTermID here’s a refined DAX measure that should give you accurate results across all visuals, even with slicing:

Claims in Last 3 Years = 
VAR SelectedTerm = SELECTEDVALUE('Policy Term'[PolicyTermID])
VAR CurrentPolicyNumber = 
    CALCULATE(
        MAX('Policy Term'[PolicyNumber]),
        'Policy Term'[PolicyTermID] = SelectedTerm
    )
VAR RenewalDate =
    CALCULATE(
        MAX('Fact Policy'[RenewalDate]),
        'Fact Policy'[PolicyTermID] = SelectedTerm &&
        'Fact Policy'[PolicyNumber] = CurrentPolicyNumber
    )
VAR StartDate = EDATE(RenewalDate, -36)
RETURN
CALCULATE(
    COUNTROWS('Claims'),
    'Claims'[IncidentDate] >= StartDate &&
    'Claims'[IncidentDate] <= RenewalDate,
    'Claims'[PolicyNumber] = CurrentPolicyNumber,
    'Claims'[PolicyTermID] = SelectedTerm
)

 

If your Claims table doesn’t have PolicyTermID, you can replace that part with a TREATAS statement to bridge it through dimension logic.

 

Freece4802's avatar
Freece4802
Frequent Visitor
1 year ago

Hi rohit1991  - I applied the above and when looking at the results they all returned a blank record, I then returned the SELECTEDVALUES and this too returned a blank value, does the above not require the policy term to be within the context of the visual? perhaps this is why?