Claim completion factor and IBNR

Hi jdbuchanan71 ,

I create a calculation table and calculation column, the formula is as follows:

Table = 
SUMMARIZE(
    Claims,
    Claims[Group],
    Claims[Lag],
    'Incurred Dates'[Incurred Month]
)

Completion Factor = 
DIVIDE(
    CALCULATE(
        SUM(Claims[Paid Amount]),
        FILTER(
            'Claims',
            'Claims'[Lag] <= 'Table'[Lag]
            && Claims[Group] = 'Table'[Group]
            && EOMONTH(Claims[Service Date],0) = 'Table'[Incurred Month]
        )
    ),
    CALCULATE(
        SUM(Claims[Paid Amount]),
        FILTER(
            ALLSELECTED(Claims),
            Claims[Lag] <= 11
            && Claims[Group] = 'Table'[Group]
            && EOMONTH(Claims[Service Date],0) = 'Table'[Incurred Month]
        )
    )
)

After filtering the month and group, the factor obtained are as follows:

Then create the following measures:

Avg Completion Factor = 
var StartDate = 
    CALCULATE(
        MIN('Incurred Dates'[Incurred Month]),
        FILTER(
            Claims,
            NOT(ISBLANK([IBNR Lag]))
        )
    )
var EndDate = 
    CALCULATE(
        MAX('Incurred Dates'[Incurred Month]),
        FILTER(
            Claims,
            NOT(ISBLANK([IBNR Lag]))
        )
    )
return 
    CALCULATE(
        AVERAGE('Table'[Completion Factor]),
        FILTER(
            'Table',
            'Table'[Group] = MAX(Claims[Group])
            && 'Table'[Lag] = Claims[IBNR Lag]
            && 'Table'[Incurred Month] >= EDATE(StartDate,-12)
            && 'Table'[Incurred Month] <= EDATE(EndDate,-11)
        )
    )

Total Incurrented Estimate = [Paid To Date]/[Avg Completion Factor]

IBNR Estimate = 
IF(
    HASONEVALUE('Incurred Dates'[Incurred Month]),
    [Total Incurrented Estimate] - [Paid To Date],
    [Paid To Date]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.