Forum Discussion

ElkanaTheGreat's avatar
ElkanaTheGreat
Frequent Visitor
7 years ago
Solved

Dynamically link earned premiums table to policy table

In insurance it is useful to see the premium that is being earned over a given period. For example if an insurance policy is sold for $1000 a year it will earn $2.74 every day.

 

I want to be able to show a line graph of earned premiums over time with the ability to add different policy factors to the legend.

 

This as an example of the data I have.

 

PolicyNumberPremiumPaidpolicystartpolicyendPremiumPerDayVehicle Type
1100001 January 201831 December 20182.7Car
2400007 March 201806 March 201911Van

 

I have created a daily calendar table using the DAX function CALENDAR and summed the PremiumPerDay for active policies which looks like this. 

DatePremium
01/01/2018 00:002.7
02/01/2018 00:002.7
03/01/2018 00:002.7
04/01/2018 00:002.7
05/01/2018 00:002.7
06/01/2018 00:002.7

 

It is now simple to create a line graph showing earned premiums over time using just the date table. However there is no way for me to drag the vehicle type field into the legend of this graph. 

 

Is there a way for me to structure/link my data in Power BI in a different way so that I can add factors like the vehicle type into the legend of this graph quickly and dynamically?

 

To see this sample data more clearly see here.

https://www.dropbox.com/s/zewoxg0gcl4mdl6/PremiumEarning.pbix?dl=0

 

 

Thanks so much.

  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    ElkanaTheGreat

     

    Try this new file which is grouped by month:

     

    https://1drv.ms/u/s!AiiWkkwHZChHjylF131vK9FcAbY9

     

    The new grouped table can be generated like this:

     

     

    PremiumsMonthly =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                Table1,
                VAR PolStart = Table1[policystart]
                VAR PolEnd = Table1[policyend]
                RETURN
                    GENERATESERIES (
                        1,
                        DATEDIFF ( EOMONTH ( PolStart, 0 ), EOMONTH ( PolEnd, 0 ), MONTH ) + 1
                    )
            ),
            "Days", 1
                + MIN ( Table1[policyend], EOMONTH ( Table1[policystart], [Value] - 1 ) )
                - MAX ( EOMONTH ( Table1[policystart], [Value] - 2 ) + 1, Table1[policystart] ),
            "Month", EOMONTH ( Table1[policystart], [Value] - 1 )
        ),
        "PolicyNumber", Table1[PolicyNumber],
        "Premium", Table1[PremiumPerDay] * [Days],
        "VehicleType", Table1[Vehicle Type],
        "MonthDate", [Month],
        "Days", [Days]
    )

10 Replies