Mark White's BI Blog

In a  previous post about earned premium , I outlined how you could calculate a monetary value based on a period of time over which it was earned using DAX. Serendipitously, the next day a colleague forwarded Alberto Ferrari's paper on understanding DAX query plans , and after giving it a thorough read I fired up the query profiler and set out to optimize our calculated measure for earned premium. Alberto's paper details a performant solution to the classic events in progress problem, of which earned premium  is a close cousin. My excitement at lazily shoplifting Alberto's work came to a grinding halt when I discovered that his 40ms Jedi solution only worked if data was queried at a specific granularity. This wasn't going to cut it... we need an earned premium measure that works at any level of aggregation. Back to the drawing board. It turns out that much of Alberto's advice is (as always) really valuable. While I strongly recommend reading Alberto's pa...

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