Forum Discussion

Asfaa5's avatar
Asfaa5
Frequent Visitor
6 years ago

Claim Frequency & Claim Forecasting

Hi Team,

 

Im preparing the Claims Forecasting for 2020. I have 4 Tables(Sales, Claims, Product & Calendar).

 

In Sales Table (Contract Number, Contract Start Date, Contract End Date)
In Claims Table (Claims Date, Status, Contract Number)

In Product Table (Product Name, Model, Contract Number, Make)

 

Program has started from 2018 and claim as well.

 

I have prepared Measure for calculating Active Contracts on Monthly Basis,

 

1. New Adds = CALCULATE(COUNTROWS('Sales'))

2. Policy End = CALCULATE(COUNTROWS('Sales'), USERELATIONSHIP(Sales[Contract_End_Date__c],'Calendar'[Date]))

3. Policy Remaining Count = ABS([New Adds]-[Policy End])

4. Active Policies = CALCULATE(SUMX(SUMMARIZE('Calendar','Calendar'[Month], "ABCD", ABS([Policy Remaining Count])),[ABCD]), DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date])))
 
For Claims,
1.Total Claims = CALCULATE(COUNTROWS(Claims), FILTER(Claims, Claims[Status] <> "Closed - Rejected"))
2.Claim Frequency = DIVIDE([Total Claims],[Active Policies])
Claim Frequency gives me exact Frequency on monthly basis.
 
Now i need to forecast claims count for 2020 taking base of Claim Frequency of 2019(Month on Month).
Basically, I need to calculate Active Policies of Jan-20 * Claim Frequency of Jan-19, Active Policies of Feb-20 * Claim Frequency of Feb-19, Like this till Dec-20.
 
I need help in Measures for calculating Claim Frequency of Only 2019 from Active policies of 2019, then use that Measure to Forecast 2020 Claims Count.
 
I created this Measure, 
Claims Frequency 2019 = DIVIDE(MAXX(FILTER('Calendar', 'Calendar'[Year] = 2019),[Total Claims]), MAXX(FILTER('Calendar', 'Calendar'[Year] = 2019), [Active Policies]))
But it doesnt give proper frequency like "Claim Frequency" Measures. can anyone help me on this and how to forecast 2020 Claims by taking 2019 as base.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi. As much as I understand, your most granular level on which you calculate forecasts is month. If you want to have an estimate for a year, isn't it just enough to add up all the forecasts for all the year's months?

    Best
    D
    • Asfaa5's avatar
      Asfaa5
      Frequent Visitor
      6 years ago

      Anonymous : Im trying to forecast on Mothly basis of 2020.

       

      Ex : If Claim Frequency of Jan'19 is 10% and Active Police of Jan'20 is 10000, then Forecast of Jan'20 should be 1000.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Yeah, I know how you calculate a forecast based on the PY period. But I have not got an answer to my question: Why can't you just sum all the forecasts for all the months of 2020? If you know the percentage for each month of 2019, then it's easy to do this:

        sum over all i = 1,2,...,12 (all months of 2019):
        claim_freq(i) * active_policies(i+1year)

        Why can't you use it?

        Best
        D