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!

     
AlB's avatar
AlB
Community Champion
5 years ago

Hi rocko95 

Earned Premium Measure =
MIN ( 'PolicyBase Package'[1 Day Premium] )
    * (
        MIN ( 'PolicyBase Package'[Active To] )
            - MIN ( 'PolicyBase Package'[effectivedate] ) + 1
    )

and potentially use a one column table in the other measure, the one column that defines the row

Accumulated EP =
SUMX ( DISTINCT ( 'PolicyBase Package'[KeyColumn] ), [Earned Premium Measure] )

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

rocko95's avatar
rocko95
Frequent Visitor
5 years ago

Hello AIB,

Thanks for your reply!

 

As mentioned above, I need disconnected calendar table, so I can dynamically change the period. for example, if I choose specific month on the slicer, the measure should recalculate for the selected period. In your suggestion DAX measure there is no disconnected calendar. 

maybe SUMMARISE can help me? I don't know well how to use that function.