Forum Discussion

rocko95's avatar
rocko95
Frequent Visitor
5 years ago
Solved

DAX Earned Premium Calculation Optimization

Hello all,   I'm trying to calculate earned premium / loss ratio in DAX. I need to calculate how many days was active each row dynamically and multiplied by premium amount.   This is the first f...
Optimizing DAX
  • rocko95's avatar
    rocko95
    5 years ago

    Thans everybody for your time!

    I have found this Topic  and this helped me exactly how I wanted. Instead of measure or column, I created a table and works perfectly and fast.

     

    This is the table formula that i have used 

    Earned Premium Table = 

    SELECTCOLUMNS (

     ADDCOLUMNS (

     GENERATE (

     'PolicyBase Package',

     VAR PolStart = 'PolicyBase Package'[effectivedate]

     VAR PolEnd = IF('PolicyBase Package'[Active_To] > TODAY() , TODAY() , 'PolicyBase Package'[Active_To])

     RETURN

     GENERATESERIES (

     1,

     DATEDIFF ( EOMONTH ( PolStart, 0 ), EOMONTH ( PolEnd, 0 ), MONTH ) + 1 

     )

     ),

     "Days", 1

     + MIN ( 'PolicyBase Package'[Active_To], EOMONTH ( 'PolicyBase Package'[effectivedate], [Value] - 1 ) )

     - MAX ( EOMONTH ( 'PolicyBase Package'[effectivedate], [Value] - 2 ) + 1, 'PolicyBase Package'[effectivedate] ),

     "Month", EOMONTH ( 'PolicyBase Package'[effectivedate], [Value] - 1 )

     ),

     "PolicyNumber", 'PolicyBase Package'[policynumber],

     "Premium", 'PolicyBase Package'[1 Day Premium] * [Days],

     "MonthDate", [Month],

     "Days", [Days]

    )

     

    Thanks!

     
PaulOlding's avatar
PaulOlding
Solution Sage
5 years ago

If you're only going to allow a single continous period to be selected in Calendar then you could use this measure:

 

Accumulated EP =
VAR _MinSelected = MIN('Calendar'[Date])
VAR _MaxSelected = MAX('Calendar'[Date])
VAR _Result = 
SUMX(
    'PolicyBase Package',
    VAR _CalcMin = MAX('PolicyBase Package'[effectivedate], _MinSelected)
    VAR _CalcMax = MIN('PolicyBase Package'[Active To], _MaxSelected)
    RETURN IF(_CalcMin <= _CalcMax, (INT(_CalcMax - _CalcMin) + 1) * 'PolicyBase Package'[1 Day Premium], 0)
)
RETURN
    _Result

 

 

An alternative would be to have a different fact table that expands out your PolicyBase Package table to have one row for each policy for each date between effectivedate and applies_to.

You would then link Calendar to the 'Earned Date' column in the fact.  Your Accumulated EP measure would then just be = SUM('New Fact Table'[1 Day Premium])

Assuming your 40,000 policies run for 1 year each then that comes to around 14.5 million rows (40,000 * 365).

 

You could use Power Query to create the new fact table:

Source = PolicyBase Package

#"Added Custom" = Table.AddColumn(Source, "Earned Date", each { Number.From([effectivedate])..Number.From([applies_to]) }),
#"Expanded Earned Date" = Table.ExpandListColumn(#"Added Custom", "Earned Date"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Earned Date",{{"Earned Date", type date}})