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

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.

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