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!

     
rocko95's avatar
rocko95
Frequent Visitor
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!