topic Re: Claim Frequency & Claim Forecasting in DAX Commands and Tips

Claim Frequency & Claim Forecasting

Asfaa5 — Tue, 03 Mar 2020 06:37:31 GMT

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.

Re: Claim Frequency & Claim Forecasting

Anonymous — Tue, 03 Mar 2020 09:03:37 GMT

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

Re: Claim Frequency & Claim Forecasting

Asfaa5 — Tue, 03 Mar 2020 09:13:33 GMT

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.

Re: Claim Frequency & Claim Forecasting

Anonymous — Tue, 03 Mar 2020 09:53:02 GMT

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

Re: Claim Frequency & Claim Forecasting

Asfaa5 — Tue, 03 Mar 2020 11:36:55 GMT

Hi,

I am using this measure now

1.Claim Frequency LY = Calculate([Claim Frequency], SAMEPERIODLASTYEAR(Calendar[Date])

2. Forecast = [Total Active Policies]*[Claim Frequency LY]

In Visual im filtering only Year 2020 and Month wise breakup.