I have Premium for a policy with TransactionEffectiveDate and TransactionExpirationDate.
Is any way to write query in DAX that would take each premium amount and break it down by the month between those two dates.
The @AsOfDate parameter will be the date the policy stops Earning.
For Example:
PolicyNumber WIC1000158-00
has Premium of $82,913
and TransactionEffectiveDate 1/5/2018
and TransactionExpirationDate 1/5/2019.
which gives us total of 365 days of Policy life.
Let see how much we Earned till '06/29/2018'
So Premium of 82,913 should be broken down by 12 month:
So for the first Policy month we have only 27 days
So 82,913/365 * 27 = 6,133.29 -thats how much its been earned in the first month.
And so on until the @AsOfDate.
The result should be like that with @AsOfDate = '6/29/2018':
Although I only need columns
YearNum,
MonthNum,
Qtr,EarnedPremium
I was able to write it in SQL but Is any way to achieve that in DAX?
.pbix file is availabel here: https://www.dropbox.com/s/pbj61vsb20qbhzm/LossTriangleTest.pbix?dl=0
UPDATE!
Alexis, thank you very much. Its amazing what DAX can do with the numbers.
For some reason its always gives me a Earned Premium total slightly more than original Premium.
For example PolicyNumber 'PACA1000101-00' has total premium $10,568 but calculates Earned as $10,596. April 2013 gives slightly more.
I think some additional logic need to be implemented towards the last breakdown (EndRiskMonth).
(Result from Power BI. Sorry, its not sorted yet)
Result from SQL:
As you can see April 2013 has 26 days.
This is how I do it in SQL, if it help. (can be run in SSMS)
DECLARE @PlazaInsuranceWPDataSet TABLE (
PolicyNumber varchar(50),
TransactionEffectiveDate datetime,
TransactionExpirationDate datetime,
WrittenPremium money
)
INSERT INTO @PlazaInsuranceWPDataSet values ('PACA1000101-00', '2012-04-27','2013-04-27',6630.00 ),
('PACA1000101-00', '2012-04-27','2013-04-27',1600.00 ),
('PACA1000101-00', '2012-04-27','2013-04-27',490.00 ),
('PACA1000101-00', '2012-04-27','2013-04-27',-77.00 ),
('PACA1000101-00', '2012-04-27','2013-04-27',1925.00 )
; WITH Earned_to_date AS (
SELECT Cast('2019-06-30' AS DATE) AS Earned_to_date
), policy_data AS (
SELECT
PolicyNumber
, Cast(TransactionEffectiveDate AS DATE) AS TransactionEffectiveDate
, Cast(TransactionExpirationDate AS DATE) AS TransactionExpirationDate
, WrittenPremium
FROM @PlazaInsuranceWPDataSet
)
, digits AS (
SELECT digit
FROM (VALUES (0), (1), (2), (3), (4)
, (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (
SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
FROM digits AS d1
CROSS JOIN digits AS d2
CROSS JOIN digits AS d3
CROSS JOIN digits AS d4
), calendar AS (
SELECT
DateAdd(month, number, '1753-01-01') AS month_of
, DateAdd(month, number, '1753-02-01') AS month_after
FROM numbers
), policy_dates AS (
SELECT
PolicyNumber
, CASE
WHEN month_of < TransactionEffectiveDate THEN TransactionEffectiveDate
ELSE month_of
END AS StartRiskMonth
, CASE
WHEN TransactionExpirationDate < month_after THEN TransactionExpirationDate
WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
ELSE month_after
END AS EndRiskMonth
, DateDiff(day, TransactionEffectiveDate, TransactionExpirationDate) AS policy_days
, WrittenPremium
FROM policy_data
JOIN calendar
ON (policy_data.TransactionEffectiveDate < calendar.month_after
AND calendar.month_of < policy_data.TransactionExpirationDate)
CROSS JOIN Earned_to_date
WHERE month_of < Earned_to_date
)
SELECT PolicyNumber,
StartRiskMonth,
EndRiskMonth,
YEAR(StartRiskMonth) as YearNum,
MONTH(StartRiskMonth) as MonthNum,
DATEPART(qq, StartRiskMonth) AS Qtr,
policy_days,
sum(WrittenPremium) as WrittenPremium,
DateDiff(day, StartRiskMonth, EndRiskMonth) AS DaysInMonth,
sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / NULLIF(policy_days,0)) as EarnedPremium
FROM policy_dates
GROUP BY PolicyNumber, StartRiskMonth, EndRiskMonth
, DateDiff(day, StartRiskMonth, EndRiskMonth),policy_days
ORDER BY PolicyNumber, StartRiskMonth
UPDATE!
Alexis,
I have modified "EoMonth" in Calendar table EOMONTH([Month], 0)+1 --added + 1
In a CrossTabel "DaysInMonth", [EndRiskMonth] - [StartRiskMonth], --took off 1
And for Earned Premium
"EarnedPremium", [Premium] *
DIVIDE([EndRiskMonth] - [StartRiskMonth] , [End] - [Start])) --took off 1.
Result now looks like this:
