Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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!!
Solved! Go to Solution.
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.
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.
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
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.
Hope this helps.
Gus.
Extremely helpful, thanks Gus!
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
@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.
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
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.
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
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.
Hi,
My pleasure. That's great! Thank you for sharing this with us!
Best Regards!
Dale
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!
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?
Proud to be a Super User!
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.