Forum Discussion

gguadalupe's avatar
gguadalupe
Frequent Visitor
9 years ago
Solved

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

 

 

 

  • gguadalupe's avatar
    gguadalupe
    9 years ago

    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.

22 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

     

    • gguadalupe's avatar
      gguadalupe
      Frequent Visitor
      9 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        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

    • MWinter225's avatar
      MWinter225
      Advocate IV
      7 years ago

      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's avatar
        gguadalupe
        Frequent Visitor
        7 years ago

        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.

    • Actuary's avatar
      Actuary
      Frequent Visitor
      2 years ago

      Hey, can you help me?
      How should I go about this if the premium IDs are not unique?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      gguadalupe
      Frequent Visitor
      4 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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

  • spuder's avatar
    spuder
    Resolver IV
    9 years ago

    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.

  • gguadalupe's avatar
    gguadalupe
    Frequent Visitor
    9 years ago

    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!

     

     

  • PowerPaddy's avatar
    PowerPaddy
    Frequent Visitor
    9 years ago

    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's avatar
      gguadalupe
      Frequent Visitor
      9 years ago

      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.