Skip to main content

DAX and Insurance's Earned Premium problem

In the world of short term insurance, "Earned Premium" is a common BI metric. In its simplest form, an amount of money is earned as time elapses through a period of cover. On any given day, you will have earned some, all or none of the premium paid.

Using DAX calculations, solving earned premium turns out to be both easy and efficient, and in this post I'll show you how to do it. Before you start, download and open this Excel 2013 file. Make sure that you have the PowerPivot add-in enabled in Excel.

If you open the PowerPivot cube, you'll find two tables, the first being a regular "Date" table used to represent the hierarchy of days, months, years etc. The second "Premium" table contains the data that's of interest, and to keep things simple, I've only included 4 columns:

  • Product Line   - describes a type of insurance product
  • Amount           - the amount of premium paid by a policy holder for a given period of cover.
  • Start Date        - the date when insurance cover starts for the premium paid.
  • End Date         - the date after which insurance cover ends for the premium paid.

The DAX calculation, Earned Premium is where it get interesting. The layman's calculation for earned premium is:

Earned Premium  = Amount Paid * Days In Current Period / Total Days of Cover

For example, if you paid $100 for 1 year of insurance, then in the month of March you will earn $8.49:

Earned Premium = $100 * 31 (Days in March)  / 365 (Days of insurance purchased)

To move into the world of DAX, the above equation is pseudo coded as follows:

Earned Premium = SUM(
        Amount
        * [Days in Date table overlapping period of cover]
        / [Days in Date table for total period of cover]
)

Step 1: Calculate Days in Total Period Of Cover

The DAX technique for calculating the number of days in a period is to produce a table of dates that fall within a period using DATESBETWEEN, and then counting the number of rows:

COUNTROWS( DATESBETWEEN( Dates, Start Date, End Date )  ) 

Filling in the actual table and column names, the DAX formula becomes:

COUNTROWS ( DATESBETWEEN( 'Date'[Date], 'Premium'[Start Date], 'Premium'[End Date] )  ) 

Step 2: Calculate Days in Current Period 

Determining which days fall into the current period of cover requires checking for overlap between the period currently being calculated, and the total period of cover. In practice, this means using the latest of the two start dates and the earliest of the two end dates:

COUNTROWS (
   DATESBETWEEN (
      'Date'[Date],
      IF(FIRSTDATE('Date'[Date]) > 'Premium'[Start Date], FIRSTDATE('Date'[Date]), 'Premium'[Start Date] ) ,
      IF(LASTDATE ('Date'[Date]) < 'Premium'[End Date] , LASTDATE ('Date'[Date]), 'Premium'[End Date] )
   )
)

Step 3: Optimizing the input data

No relationship is defined between the Date and Premium tables - so to improve the performance of our calculation, we give DAX a way of quickly eliminating records that are not applicable to our calculation. The logic for doing this is:

  • Filter out records where cover ends before the period we're calculating starts.
  • Filter out records where the cover starts after the period we're calculating ends.
  • Group the remaining records by start and end date, projecting the sum total of premium for the given period.

In DAX this looks somewhat inside out...

FILTER (
   SUMMARIZE (
      'Premium',
      'Premium'[Start Date],
      'Premium'[End Date],
      "EarnedPremium",
      SUM('Premium'[Amount])
      ),
   'Premium'[Start Date] <= LASTDATE('Date'[Date]) && 'Premium'[End Date] >= FIRSTDATE('Date'[Date])


In T-SQL, this might look as follows:

SELECT [Start Date], [End Date], [EarnedPremium] = SUM(Amount)
FROM Premium
GROUP BY [Start Date],[End Date]
HAVING [Start Date] < {Current Cell in Excel's Period End}
AND [End Date] > {Current Cell in Excel's Period Start}

This calculation efficiently reduces the number of times our earned premium expression needs to be run inside of the SUM statement. The completed DAX calculation then:

Earned Premium:=SUMX (
   FILTER (
      SUMMARIZE (
         'Premium',
         'Premium'[Start Date],
         'Premium'[End Date],
         "EarnedPremium",
         SUM('Premium'[Amount])
      ),
   'Premium'[Start Date] <= LASTDATE('Date'[Date]) && 'Premium'[End Date] >= FIRSTDATE('Date'[Date])
   ),
   [EarnedPremium]
   *
   COUNTROWS (
      DATESBETWEEN (
         'Date'[Date],
         IF(FIRSTDATE('Date'[Date]) > 'Premium'[Start Date], FIRSTDATE('Date'[Date]), 'Premium'[Start Date] ) ,
         IF(LASTDATE ('Date'[Date]) < 'Premium'[End Date] , LASTDATE ('Date'[Date]), 'Premium'[End Date] )
      )
   ) /
   COUNTROWS ( DATESBETWEEN( 'Date'[Date], 'Premium'[Start Date], 'Premium'[End Date] ) )
)

Post a Comment

Popular posts from this blog

Reading Zip files in PowerQuery / M

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usable technique. Inspired by the possibilities revealed in Ken's solution, but frustrated by slow performance, I set out to learn the M language and write a faster alternative.
UnzipContents The result of these efforts is an M function - UnzipContents - that you can paste into any PowerBI / PowerQuery report. It takes the contents of a ZIP file, and returns a list of files contained therein, along with their decompressed data:



If you're not sure how to make this function available in your document, simply:

Open up PowerQuery (either in Excel or in PowerBI)Create a new Blank Query.Open up the Advanced Editor  (found on the View tab in PowerBI).Copy-Paste the above code into the editor, then close the editor.In the properties window, rename the the function to UnzipContents Usage Using the function is fairly straight forward: Choose "New Quer…

SQL Server vs Azure Data Warehouse vs Netezza vs Redshift

The Great Database Shoot Out In Jan'17, I teamed up with Marc van der Zon (Data Scientist), to test and compare several database technologies for our organization's future analytics and BI platform. The technologies that made the shortlist were:
SQL Server, because it is the organization's existing BI platform.Azure Data Warehouse, because of its high similarity to SQL Server.Amazon's Redshift, because of its attractive cost, scalability and performance.Netezza, because it is anaffordable on-prem appliance that performs well. Considerations We were primarily looking for the best bang-for-buck option, so price and performance were viewed as more important than how feature complete any particular option was. That said, what we regarded as important included: A shallow learning curve for BI developers (i.e. no need for expert skills in order to get good performance)
Ability to re-use existing code  (i.e. ANSI-92 SQL compatibility)
Time-to-solution.   (i.e. does the platform …

Easily Move SQL Tables between Filegroups

Recently during a Data Warehouse project, I had the need to move many tables to a new file group. I didn't like any of the solutions that I found on Google, so decided to create on of my own. The result?

MoveTablesToFilegroupClick here for a nifty stored proc allows you to easily move tables, indexes, heaps and even LOB data to different filegroups without breaking a sweat. To get going, copy-paste the code below into Management Studio, and then run it to create the needed stored procedure.
Hopefully the arguments are self explanatory, but here are some examples:

1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY:
EXEC dbo.sp_MoveTablesToFileGroup
@SchemaFilter = '%',-- chooses schemas using the LIKE operator
@TableFilter  = '%',-- chooses tables using the LIKE operator
@DataFileGroup = 'SECONDARY',-- The name of the filegroup to move index and in-row data to.
@ClusteredIndexes = 1,-- 1 means "Move all clustered inde…