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!

     
AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

It seems like you could create a calculated column

 

DaysActive = 'PolicyBase Package'[Active To] - 'PolicyBase Package'[effectivedate] + 1

 

and then redefine

 

Accumulated EP = SUMX ( 'PolicyBase Package', [1 Day Premium] * [DaysActive] )

 

If that's still too slow, define the product [1 Day Premium] * [DaysActive] as a new calculated column [TotalPremium] and write

 

Accumulated EP = SUM ( 'PolicyBase Package'[TotalPremium] )

  • rocko95's avatar
    rocko95
    Frequent Visitor
    5 years ago

    Hello AlexisOlson,

     

    Thank you so much for your reply!

     

    That would be the perfect solution if i didn't want dynamic results. That column will write the total sum of how many days was the policy active, but what if I want to know how many days was the policy active in specific month and year? Thats why I need measure and not column.

     

    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.