Custom Column Only Works for Full Year

Hi kellanbochenek 

It seems that the issue may stem from how 'Effective_Net_Exposure' handles dates when policies span multiple years. Since 2023 is correct, but 2024 has discrepancies, it could be due to how "Pol" and "End" records from prior years are factored into your current year logic. Here are some adjustments you can try:

• Ensure that the "Pol" record you’re summing 'EndNetExposure' with is indeed from the current year. You could add a filter to check that "Pol" records are only added if they’re within the current year.
  
• Ensure that the "Pol" record you’re summing 'EndNetExposure' with is indeed from the current year. You could add a filter to check that "Pol" records are only added if they’re within the current year.

Updated Dax:

Effective_Net_Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR CurrentYear = YEAR(CurrentReceivedDate)
VAR PolNetExposure = 'FactPolicyEndorsement'[NetExposure]
VAR EndNetExposure_SameYear =
CALCULATE(
SUM('FactPolicyEndorsement'[NetExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear
)
)
VAR EndNetExposure_DiffYear =
CALCULATE(
SUM('FactPolicyEndorsement'[NetExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
YEAR('FactPolicyEndorsement'[ReceivedDate]) <> CurrentYear
)
)
RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_SameYear,
IF(
CurrentRecordType = "End" &&
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear
)
) > 0,
0,
'FactPolicyEndorsement'[NetExposure]
)
)

Best Regards,

Jayleny

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