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

Earned Premium Measure = MIN('PolicyBase Package'[1 Day Premium]) *

CALCULATE(

COUNTROWS('Calendar') ,

FILTER(

'Calendar',

'Calendar'[Date] >= MIN('PolicyBase Package'[effectivedate]) &&

'Calendar'[Date] <= MIN('PolicyBase Package'[Active To])

))

This is the second formula -

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

Second formula is to calculate previous measure for each row and sum up in total.

I have only about 40k rows but it takes a lot of time to calculate and shows error on the web - " Visual has exceeded the available resources "

I have tried a lot of other variants and nothing helps. please help me to optimize DAX measure. the main problem is to dinamically calculate how many days was active each row. Creating a column does not help, because it cant be dynamic for selected period.

unfortunatelly data is confidentional and can not share with yo

Thank you in advance.

Optimizing DAX

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!

8 Replies

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}})
- AlexisOlson
  Super User
  5 years ago
  This looks pretty good to me. It might benefit from filtering 'PolicyBase Package' before calculating the sumproduct like this:
  
  Accumulated EP = VAR _MinSelected = MIN ( 'Calendar'[Date] ) VAR _MaxSelected = MAX ( 'Calendar'[Date] ) RETURN SUMX ( FILTER ( 'PolicyBase Package', 'PolicyBase Package'[effectivedate] <= _MaxSelected && 'PolicyBase Package'[Active To] >= _MinSelected ), VAR _CalcMin = MAX ( 'PolicyBase Package'[effectivedate], _MinSelected ) VAR _CalcMax = MIN ( 'PolicyBase Package'[Active To], _MaxSelected ) RETURN ( INT ( _CalcMax - _CalcMin ) + 1 ) * 'PolicyBase Package'[1 Day Premium] )
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
  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
    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.
AlB
Community Champion
5 years ago
rocko95

I don't follow. If you share a sample of the relevant tables, just with mock data, and explain what you are attempting and show the expected result, it's easier.

In the meantime, try this:

Earned Premium Measure = MIN ( 'PolicyBase Package'[1 Day Premium] ) * COUNTROWS ( DATESBETWEEN ( 'Calendar'[Date], MIN ( 'PolicyBase Package'[effectivedate] ), MIN ( 'PolicyBase Package'[Active To] ) ) )

and potentially, as earlier:

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

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!

Forum Discussion

DAX Earned Premium Calculation Optimization

8 Replies

Related content

Fabric Cost optimization

Optimize Measures

DAX performance optimization

Dax code optimization

DimUserAccess Optimal Design