Dynamically link earned premiums table to policy table

[ElkanaTheGreat]

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.

PolicyNumber	PremiumPaid	policystart	policyend	PremiumPerDay	Vehicle Type
1	1000	01 January 2018	31 December 2018	2.7	Car
2	4000	07 March 2018	06 March 2019	11	Van

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

Date	Premium
01/01/2018 00:00	2.7
02/01/2018 00:00	2.7
03/01/2018 00:00	2.7
04/01/2018 00:00	2.7
05/01/2018 00:00	2.7
06/01/2018 00:00	2.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]

Hi  @ElkanaTheGreat !

please find the file below, let mke know if this is what you want

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

[ElkanaTheGreat]

Hi thanks for this it is an interesting solution but will be extremely memory intensive for lots of policies. Is there a way of doing this without adding extra rows for each day?

Thanks

[LivioLanzo]

@ElkanaTheGreat

the table I am creating could be further aggregated by vehicle type so that the number of rows is drastically reduced. Could you upload a dataset including a bit more policies / vehicles types?

[ElkanaTheGreat]

This wouldn't help much as I have more factors than just vehicle type so table will get exponentially bigger with each added factor.

Perhaps it is just better to earn by month only so that the table won't get too big.

Is it possible to use GENERATE to get the months between start and end date rather than like you did with CALENDAR which gets all the days.

[LivioLanzo · Accepted solution]

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

[Ashish_Mathur]

Hi,

It looks like my statments in the previous messahe were incorrect.  Without creating one row for each date, there can be a solution provided you would not want to see day wise premium collected.  You may download the PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

On the x axis of the graph, will you always want to show Months only?

[ElkanaTheGreat]

Would want to be able to show everything but I guess I'd hardly use days so months would be a good second best

[Ashish_Mathur]

Hi,

Though i have not tried an alternative solution diligently, my guess is that the DAX formula for that would be very intensive (if we do not break out the table into individual date rows).  This is because we would have to create an inactive relationship between policyend date and the Date column of the Calendae Table.  Furthermore, by breaking out the Table into individual date wise rows, we can use other Date/Time intelligence functions.

I tried to create one row per month instead of one row per date but then the problem is that if we assign the first date of the month to the row, then we are assuming that the plicy start date is the first date (which in many cases will not be true).

Sorry but cannot help much here.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.