Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 -
Solved! Go to 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!
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!
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}})
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]
)
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. |
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] )
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.
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. |
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.
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |