DAX Earned Premium Calculation Optimization

[rocko95]

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.

[rocko95 · Accepted solution]

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]

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]

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]
    )

[AlB]

@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.

[AlexisOlson]

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]

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]

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]

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.