Forum Discussion
Insurance Earned Premium/Loss Ratio Calculation
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,
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
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.
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
Hey, can you help me?
How should I go about this if the premium IDs are not unique?
