如何计算直到@AsOfDate 的两个日期之间的已赚保费，并在 DAX 中按月细分 [英] How to calculate Earned Premium between two dates till @AsOfDate and break it down by each month in DAX

问题描述

我对具有 TransactionEffectiveDate 和 TransactionExpirationDate 的策略有 Premium.

有什么方法可以在 DAX 中编写查询，将每个保费金额按这两个日期之间的月份细分.

@AsOfDate 参数将是保单停止获利的日期.

例如:

PolicyNumber WIC1000158-00保费为 $82,913和 TransactionEffectiveDate 1/5/2018

和 TransactionExpirationDate 1/5/2019.

这为我们提供了总共 365 天的保单有效期.

让我们看看截至 '06/29/2018' 我们赚了多少钱

所以 82,913 的保费应该按 12 个月细分:

所以对于第一个保单月，我们只有 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 中运行)

声明@PlazaInsuranceWPDataSet 表(PolicyNumber varchar(50),TransactionEffectiveDate 日期时间，TransactionExpirationDate 日期时间，书面保费)插入@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 (选择演员('2019-06-30' AS DATE)作为 Earned_to_date), policy_data AS (选择保单号码, Cast(TransactionEffectiveDate AS DATE) AS TransactionEffectiveDate, Cast(TransactionExpirationDate AS DATE) AS TransactionExpirationDate, 书面溢价来自@PlazaInsuranceWPDataSet), 数字 AS (选择数字从 (值 (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 号码从数字作为 d1交叉连接数字作为 d2CROSS JOIN 数字 AS d3CROSS JOIN 数字 AS d4), 日历 AS (选择DateAdd(month, number, '1753-01-01') AS month_of, DateAdd(month, number, '1753-02-01') AS month_after从数字), policy_dates AS (选择保单号码，   案件当month_of <时TransactionEffectiveDate THEN TransactionEffectiveDateELSE month_of结束为 StartRiskMonth，   案件WHEN TransactionExpirationDate 

<小时>

更新！

亚历克西斯，

我在日历表中修改了EoMonth" EOMONTH([Month], 0)+1 --add + 1

在CrossTabel "DaysInMonth", [EndRiskMonth] - [StartRiskMonth], --took off 1

对于赚取的溢价

"EarnedPremium", [Premium] *DIVIDE([EndRiskMonth] - [StartRiskMonth] , [End] - [Start])) --起飞1.

结果现在如下所示:

解决方案

你应该能够使用一些 SUMMARIZE、ADDCOLUMNS 和 SELECTCOLUMNS 函数.

首先，按照以下几行创建一个 'Calendar' 表:

日历 = SUMMARIZE(添加列(日历自动()，"月", EOMONTH([日期], - 1) + 1),[月]，"EoMonth", EOMONTH([月], 0))

然后你可以像这样交叉加入这个表和你的 fact_Losses 表:

交叉表 =VAR 交叉表 =交叉连接(总结(fact_Losses，fact_Losses[PolicyNumber],"开始", MIN(fact_Losses[PolicyEffectiveDate]),"结束", MAX(fact_Losses[PolicyExpirationDate]),"溢价", SUM(fact_Losses[WrittenPremium])),'日历')VAR 风险期 =添加列(过滤器(交叉表，'日历'[EoMonth] >= [开始] &&'日历'[月] <= [结束]),"StartRiskMonth", IF([Start] > 'Calendar'[Month], [Start], 'Calendar'[Month]),"EndRiskMonth", IF([End] < 'Calendar'[EoMonth], [End], 'Calendar'[EoMonth]))返回选择列(风险期，"PolicyNumber", fact_Losses[PolicyNumber],"StartRiskMonth", [StartRiskMonth],"EndRiskMonth", [EndRiskMonth],"YearNum", YEAR('日历'[月]),"Qtr", ROUNDUP(MONTH('Calendar'[Month])/3, 0),"MonthNum", MONTH('日历'[月份]),书面溢价"，[溢价]，"DaysInMonth", [EndRiskMonth] - [StartRiskMonth] + 1,"EarnedPremium", [Premium] *DIVIDE([EndRiskMonth] - [StartRiskMonth] + 1, [End] - [Start]))

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屋！