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 

Freece4802's avatar
Freece4802
Frequent Visitor
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