如何计算两个日期之间的收入保费，直到@AsOfDate并按DAX每月细分 [英] How to calculate Earned Premium between two dates till @AsOfDate and break it down by each month in DAX
问题描述
对于具有 TransactionEffectiveDate 和 TransactionExpirationDate 的保单，我具有保费。
可以用任何一种方式在DAX中编写查询，该查询将获取每个保费金额并将其按两个日期之间的月份细分。
@AsOfDate参数将是策略停止收入的日期。
例如：
PolicyNumber WIC1000158-00
的保险费为$ 82,913
和 TransactionEffectiveDate 1/5/2018
和 TransactionExpirationDate 1/5/2019。
这使我们总共有365天的保单有效期。
让我们看看直到'2018年6月29日'我们赚了多少钱
所以应该将12个月的保费分成82,913：
因此，第一个保单月只有27天
所以82,913 / 365 * 27 = 6,133.29-多数民众赞成在第一个月赚了多少钱。
以此类推，直到 @AsOfDate 。
结果应该类似于@AsOfDate = '6/29/2018'：
尽管我只需要列
YearNum ，
MonthNum ，
Qtr ， EarnedPremium
我能够用SQL编写它，但是
是在DAX中实现该目标的任何方法？
.pbix文件在此处可用：
SQL的结果：
如您所见，2013年4月有26天。
如果有帮助，这就是我在SQL中的操作方式。 （可以在SSMS中运行）
DECLARE @PlazaInsuranceWPDataSet TABLE（
PolicyNumber varchar（50），
TransactionEffectiveDate datetime，
TransactionExpirationDate datetime，
WrittenPremium money
）
插入@PlazaInsuranceWPDataSet值（'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
）
，数字AS（
SELECT数字
FROM（值（0），（1），（2），（3），（4）
，（5），（6），（7），（8），（9） ）AS z2（数字）
），数字AS（
选择1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS编号
FROM digits AS d1
交叉连接数字AS d2
交叉连接数字AS d3
交叉连接数字AS d4
），日历AS（
SELECT
DateAdd（month， number，'1753-01-01'）AS month_of
，DateAdd（month，number，'1753-02-01'）AS month_after
FROM number
），policy_dates AS（
选择
P olicyNumber
，当month_of<时的情况为
TransactionEffectiveDate然后，然后TransactionEffectiveDate
ELSE month_of
结束于StartRiskMonth
，当交易到期日期为<时为
然后，在Earn_to_date.Earned_to_date<之后的交易截止日期
。月_之后THEN Earned_to_date
ELSE月_之后
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）
交叉加入Earned_to_date
WHERE month_of< Earned_to_date
）
SELECT PolicyNumber，
StartRiskMonth，
EndRiskMonth，
YEAR（StartRiskMonth）作为YearNum，
MONTH（StartRiskMonth）作为MonthNum，
DATEPART（qq，StartRiskMonth）作为Qtr，
policy_days，
sum（WrittenPremium）作为WrittenPremium，
DateDiff（day，StartRiskMonth，EndRiskMonth）AS DaysInMonth，
sum（WrittenPremium * DateDiff（day，StartRiskMonth，EndRiskMonth）/ NULLIF（policy_days，0））为EarnedPremium
FROM policy_dates
GROUP BY PolicyNumber，StartRiskMonth，EndRiskMonth
，DateDiff（day，StartRiskMonth，EndRiskMonth），policy_days
按PolicyNumber，StartRiskMonth
更新！
Alexis，
我在日历表中修改了 EoMonth EOMONTH（[Month]，0）+1 -添加+ 1
在十字交叉符 DaysInMonth，[EndRiskMonth]- [StartRiskMonth]，-开始1
并获得赚取的保费
EarnedPremium，[Premium] *
DIVIDE（[EndRiskMonth]-[StartRiskMonth]，[End]-[Start]））-开始1.
结果现在看起来像这样：
您应该可以使用 SUMMARIZE ， ADDCOLUMNS 和 SELECTCOLUMNS 函数。
首先，按照以下几行创建'Calendar'表：
日历= SUMMARIZE（
ADDCOLUMNS（CALENDARAUTO（），
Month，EOMONTH（[Date]，-1）+ 1），
[Month]，
EoMonth，EOMONTH（[Month]，0））
然后您可以将表与 fact_Losses 表交叉连接，如下所示：
CrossTable =
VAR交叉表=
CROSSJOIN（
SUMMARIZE（fact_Losses，
fact_Losses [PolicyNumber]，
Start，MIN（ fact_Losses [PolicyEffectiveDate]），
End，MAX（fact_Losses [PolicyExpirationDate]），
Premium，SUM（fact_Losses [WrittenPremium]）），
'Calendar'）
VAR RiskPeriods =
ADDCOLUMNS（
FILTER（CrossTables，
'Calendar'[EoMonth]> = [Start]& '日历'[月]< = [结束]），
StartRiskMonth，IF（[开始]>'日历'[月]，[开始]，'日历'[月]），
EndRiskMonth，IF（[[End]<'Calendar'[EoMonth]，[End]，'Calendar'[EoMonth]）））
返回SELECTCOLUMNS（RiskPeriods，
PolicyNumber，fact_Losses [PolicyNumber]，
StartRiskMonth，[StartRiskMonth]，
EndRiskMonth，[EndRiskMonth]，
YearNum，YEAR（'Calendar'[Month]），
Qtr，ROUNDUP（MONTH（'Calendar'[Month]）/ 3、0），
MonthNum，MONTH（'Calendar'[Month]），
WrittenPremium，[Premium] ，
DaysInMonth，[EndRiskMonth]-[StartRiskMonth] + 1，
EarnedPremium，[Premium] *
DIVIDE（[EndRiskMonth]-[StartRiskMonth] + 1，[End] -[开始]））
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:
You should be able to do this with some SUMMARIZE, ADDCOLUMNS, and SELECTCOLUMNS functions.
First, create a 'Calendar' table along these lines:
Calendar = SUMMARIZE(
ADDCOLUMNS(CALENDARAUTO(),
"Month", EOMONTH([Date], - 1) + 1),
[Month],
"EoMonth", EOMONTH([Month], 0))
Then you can cross-join this table with your fact_Losses table like this:
CrossTable =
VAR CrossTables =
CROSSJOIN(
SUMMARIZE(fact_Losses,
fact_Losses[PolicyNumber],
"Start", MIN(fact_Losses[PolicyEffectiveDate]),
"End", MAX(fact_Losses[PolicyExpirationDate]),
"Premium", SUM(fact_Losses[WrittenPremium])),
'Calendar')
VAR RiskPeriods =
ADDCOLUMNS(
FILTER(CrossTables,
'Calendar'[EoMonth] >= [Start] && 'Calendar'[Month] <= [End]),
"StartRiskMonth", IF([Start] > 'Calendar'[Month], [Start], 'Calendar'[Month]),
"EndRiskMonth", IF([End] < 'Calendar'[EoMonth], [End], 'Calendar'[EoMonth]))
RETURN SELECTCOLUMNS(RiskPeriods,
"PolicyNumber", fact_Losses[PolicyNumber],
"StartRiskMonth", [StartRiskMonth],
"EndRiskMonth", [EndRiskMonth],
"YearNum", YEAR('Calendar'[Month]),
"Qtr", ROUNDUP(MONTH('Calendar'[Month])/3, 0),
"MonthNum", MONTH('Calendar'[Month]),
"WrittenPremium", [Premium],
"DaysInMonth", [EndRiskMonth] - [StartRiskMonth] + 1,
"EarnedPremium", [Premium] *
DIVIDE([EndRiskMonth] - [StartRiskMonth] + 1, [End] - [Start]))
这篇关于如何计算两个日期之间的收入保费，直到@AsOfDate并按DAX每月细分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
