Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gguadalupe
Frequent Visitor

Insurance Earned Premium/Loss Ratio Calculation

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!!

 

DatesTable.png

 

PremiumTable.png

 

1 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.

Loss Ratio Star Model.png

 

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.

View solution in original post

20 REPLIES 20
Anonymous
Not applicable

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

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

 

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.

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
Not applicable

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
Frequent Visitor

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

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.

 

Triangle.png

 

Hope this helps.

 

Gus.

 

Extremely helpful, thanks Gus!

v-jiascu-msft
Employee
Employee

@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] )
        )
    )

Insurance Earned Premium Loss Ratio Calculation .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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)!

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.

 

Annotation 2019-05-08 165926.png

 

Gus.

@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

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.

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.

Loss Ratio Star Model.png

 

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.

@gguadalupe

 

Hi,

 

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

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gguadalupe
Frequent Visitor

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
Resolver IV
Resolver IV

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.

@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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.