Insurance Earned Premium/Loss Ratio Calculation

[gguadalupe]

Hi All!

I'm new to PowerBi and I'm trying to implement an insurance dashboard.

All is fine, but now I'm trying to implement the loss ratio formula controled by a slicer filled with my Dates table.

For those who are new to insurance, the Premium (Sales) is Earned over the period of the insurance policy.

Example: an annual premium of $1000, at the 6 months of coverage the Earned Premium is $500 and $83.33 per month if coverage starts the 1st day of the month.

In my mind, the formula I have to implement is something like this:

Premium Per Day = PremiumTable[PremiumAmt] / Datediff(In Days, PremiumTable[EffectiveDate], PremiumTable[ExpirationDate])

Earned Premium for Selected Period = [Premium Per Day] * Datediff(In Days, Higher Date between DatesTable[Date] and PremiumTable[EffectiveDate], Lower Date between DatesTable[Date] and PremiumTable[ExpirationDate])

Acumulated Earned Premium = [Premium Per Day] * Datediff(In Days, PremiumTable[EffectiveDate], DatesTable[Date])

Thank you for your comments!!

[Anonymous]

Hi everone,

I'm quiet advanced in insurance finances/underwriting etc, but completely new in Power BI.

Is there possibility to publish a small example file with PBI model? Of course with anonymized personal data (if exists).

It hasn;t to include many data , just few rows in every table. And of course all scripts mentioned in this topic.

Is it possible?

Br,

Jarek

[gguadalupe]

Hi Jarek,

I really don't think this is the best solution, but here's the file. --> https://drive.google.com/file/d/1rPvkPq14VYiTm3kGK0nbvmFvdD6c36D_/view?usp=sharing

The reason why I say this is becuase I'm creating a record for each policy/risk/coverage/month.

So, for 1 annual policy with 1 risk and 1 coverage, you will have 12 records, all calculated as the last day of the month.

I did it this way because I can't find the correct way of calculating the things I need, like the earned/unearned premium for the loss ratio, and because we don't have a lot of policies.

The good thing about this design, is that I don't have to do complex calculations, I leverage the out of the box functionalities of Power Bi to run simple aggregations (like sum, avg, count, etc). The more complex calculations you add, the slower your dashboard becomes.

But I will reach the day when the data will be too much data.

All data has been scrambled, so if you find James Bond as a client let me tell you, we don't insured James Bond. High risk and all...

If you have any question, please, let me know.

Gus

[KHL]

Hello Gus, 
I have been having issues calculating Earned premium in my model. 
Would you be able to share the code and the process how you got to Earnedpremiumchange?

Thank you.

[gguadalupe]

Absolutely!

But quick comment regarding the values in this model: I don’t do ANY complex calculations in this model. Everything is calculated as part of the data feed (before reaching PowerBi, in our backend system database)

I only allow PowerBi to do simple stuff like sum, average, YTD, ITD, Last 12 months, and that sort of thing, not because PowerBi has a lack of functionality, but because I don’t know how to do it in PowerBi. Also, I believe that if you implement complex calculations, you WILL affect in a negative way the user experience.

Now, let’s switch gears and let’s talk about SQL and how I calculate the Earned Premium Change. At a high level: I calculate the Earned Premium as of this period, and the last period at the same time, and I subtract one from the other.

In SQL, as you can imagine, I have a table with the term premium at the coverage level, with the effective, expiration, and accounting/booking date. The table looks something like this (it is much more complicated, but for the sake of the example I’m trying to keep it simple):

Transaction Type	Coverage	Effective Date	Expiration Date	Accounting Date	Term Premium
New Business	BI	01/01/2001	01/01/2002	01/01/2001	365.00

If I cancel the policy, then I will see 2 records, 1 for the new business and 1 for the cancellation. In this system, the cancellation represents the period you are cancelling.

In this example, this policy was inforce for 32 days.

Transaction Type	Coverage	Effective Date	Expiration Date	Accounting Date	Term Premium
New Business	BI	01/01/2001	01/01/2002	01/01/2001	365.00
Cancellation	BI	02/02/2001	01/01/2002	02/02/2001	-334.00

This is important to explain because my logic is based on this architecture.

Your implementation may vary depending how your system is designed.

I feed PowerBi with the information as of the last day of the month. 

So I run this query (soudo code btw) as of the last day:

@ReportDate = 01/31/2001

@ReportDateLastPeriod = 12/31/2000

select Coverage, TermPremium

-- Calculate Unearned for this Period: 01/31/2001
, case when dbo.fun_PolicyIsCancel(as of @ReportDate) = No then
      dbo.fun_CalculateUnearned(TermPremium,EffectiveDate,ExpirationDate,as of @ReportDate)
      else 0 end as [Unearned Premium]

-- Calculate Unearned for the Last Period: 12/31/2000
, case when AccountingDate <= @ReportDateLastPeriod then
      case when dbo.fun_PolicyIsCancel(as of @ReportDateLastPeriod) = No then
            dbo.fun_CalculateUnearned(TermPremium,EffectiveDate,ExpirationDate, as of @ReportDateLastPeriod)
      else 0 end
   else 0 end as [Unearned Premium Last Period]

-- Calculate Earned for this Period: 01/31/2001, using the result from the Unearned Calculation
, TermPremium – [Unearned Premium] as [Earned Premium]

-- Calculate Earned for the Last Period: 12/31/2000, using the result from the Unearned Last Period Calculation
, case when AccountingDate <= @ReportDateLastPeriod then
   TermPremium – [Unearned Premium Last Period]
   else 0 end as [Earned Premium Last Period]

-- Calculate the change in Unearned
, [Unearned Premium] – [Unearned Premium Last Period] as [Unearned Change]

-- Calculate the change in Earned
, [Earned Premium] – [Earned Premium Last Period] as [Earned Change]

from Coverage
where AccountingDate <= @ReportDate

From this query, I get the Unearned Change, and Earned Change and I send it to PowerBi with the rest of the data (Premium, OS, Incurred, Recoveries, etc, etc)

I hope this helps

Gus.

[Anonymous]

Hi Gus,

thanks a lot for quick response.

I downloaded the model and will analyze it for my purposes (mainly extended warranty, PA, Travel).

Dashboards looks quiet well.

I was looking any documents/examples in PBI connected with insurance business, but there is not to much in the web 😞 

"...we don't insured James Bond. High risk and all..." - but You know, premium could be high. Especially at the end of the year to meet the budget goals 🙂

Br,

Jarek

[PowerPaddy]

Thanks for posting this. I've been trying to acheive the exact same calculation. Have you had any success in creating a loss triangle in PowerBI? I've been looking for a Loss Triangle custom visualisation but can't find one.

Thanks

Patrick

[gguadalupe]

Hi Patrick,

With the model I posted is easy to archive a triangle.

In table vPowerBiDate, create a column: Month = FORMAT(vPowerBiDate[Date],"MMM YYYY")

And after that another: MonthOrder = FORMAT(vPowerBiDate[Date].[Date],"yyyymm")

Click Month column, click Modeling tab, click Sort By Column and select MonthOrder.

This will allow you to show the Month column with nice format and order by month value.

Do the same with accident date of table vPowerBiClaim.

With a Matrix control, choose vPowerBiDate.Month as columns and vPowerBiClaim.AccidentMonth as Rows.

In Values put the Incurred and you are good to go!

Here's an example of the final loss triangle.

Hope this helps.

Gus.

[PowerPaddy]

Extremely helpful, thanks Gus!

[v-jiascu-msft]

@gguadalupe

Hi,

Is the PremiumID of the records (rows) in PremiumTable unique? If yes, here could be the solution.

It’s better to take Premium Per Day as a calculated column. The formula is: ( pay attention to the +1 in blue. )

Premium Per Day =
'PremiumTable'[PremiumAmt]
    / (
        DATEDIFF ( 'PremiumTable'[EffectiveDate], 'PremiumTable'[ExpirationDate], DAY )
            + 1
    )

Then we are going to create two measures.

Earned Premium =
MIN ( PremiumTable[Premium Per Day] )
    * CALCULATE (
        COUNTROWS ( DateTable ),
        FILTER (
            DateTable,
            DateTable[Date] >= MIN ( PremiumTable[EffectiveDate] )
                && DateTable[Date] <= MIN ( PremiumTable[ExpirationDate] )
        )
) 

Accumulated Earned Premium =
MIN ( 'PremiumTable'[Premium Per Day] )
    * CALCULATE (
        COUNTROWS ( DateTable ),
        FILTER (
            ALL ( DateTable ),
            'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
                && 'DateTable'[Date] >= MIN ( 'PremiumTable'[EffectiveDate] )
                && DateTable[Date] <= MIN ( PremiumTable[ExpirationDate] )
        )
    )

Best Regards!

Dale

[MWinter225]

@v-jiascu-msft  that was great and really helped me a lot but then how do I SUM those values to show a total amount at the bottom? 

thanks,

Matt

UPDATE:

Got it to sum up for a grand total by wrapping my measure in a SUMX.  SUMX(table, my previous measure)!

[gguadalupe]

Hi Matt,

You mean the total amount in each column?

I let the Matrix Control to do that.

My model is as simple as posible so I try to use as much of the "out of the box" funcionalities.

Gus.

[MWinter225]

@gguadalupe  Thanks for your response Gus. Sorry for the confusion, I tagged the microsoft associate @v-jiascu-msft  in reference to his solution, which I was able to figure out and I updated my previous comment! thanks anyway!

Matt

[gguadalupe]

Hi @v-jiascu-msft

Thank you for the script.

I tried it with these 3 records and evething is perfect, but when I load the rest of the 200k records, it start giving me some weird result, like negative amounts.

I did tried another posible solution (still working on) creating a calculated table with the following script.

EarnedPremium = FILTER(
    CROSSJOIN(PremiumTable,DateTable),
    DateTable[Date] >= PremiumTable[EffectiveDate] && DateTable[Date] <= PremiumTable[ExpirationDate]
)

This gives me a table of 52 MILLON records, so I will try to finish the implementation of your solution.

Thank you again!!

Gus.

[v-jiascu-msft]

@gguadalupe

Hi,

It's complicated in the production. Please take these things below into consider.

1. The 'Premiumtable[EffectiveDate] should be less than Premiumtable[ExpirationDate];

2. DateTable should be complete and continuous;

3. The report should have at least one unique column;

4. Premium Per Day is a calculated column in the table, while Earned Premium and Accumulated Earned Premium are measures.

Best Regards!

Dale

[gguadalupe · Accepted solution]

@v-jiascu-msft

Hi Dale,

I have been thinking about this, and I finally understood that my data model was wrong.

Now the model has the info "flat" month by month, and I let Power Bi do just aggregations.

With this model now I can see the Earned Premium month by month, and calculate the Loss Ratio change month by month.

Thank you Dale for your help!!

Gus.

[v-jiascu-msft]

@gguadalupe

Hi,

My pleasure. That's great! Thank you for sharing this with us!

Best Regards!

Dale

[gguadalupe]

Hi @spuder, @vanessafvg

I forgot to mention the formula is pseudo-code, it's not DAX.

It's just my idea of what should be happening.

Thank you!

[spuder]

hi @gguadalupe

just my two cents. The datediff() Function won't return the number of days between two dates. 

So what you need is the COUNT() function and a datetable. A datetable is like a calendar and includes all dates. 

For further help I need a better explanation of the tables and what you exactly want to calculate and on which columns the result is based.

[vanessafvg]

@spuder why do you say datediff wont return the number of days?  that is the point of datediff i.e the differences in the date  and the interval will be specified by you

example  

https://blog.crossjoin.co.uk/2015/06/22/using-datediff-to-calculate-time-intervals-in-dax/

or do i misunderstand what you are saying?

[spuder]

@vanessafvg

Oh I totally apologize for my mistake. I mixed it up with dateadd()

You are totally right with your explanation. Thanks for your help. So @gguadalupe forgot what I said.