Custom Column Only Works for Full Year

[kellanbochenek]

I created a custom column (Effective_Total_Exposure) for buckets of different amount ranges and links to the key (ExposureRangeKey) which is numbered 1-16 for the specific buckets in another table. The custom column works for the full year 2023, but not for 2024, I think because of something with the dates. The Effective_Total_Exposure looks at the columns: PolicyNumber, RecordType, and ReceivedDate to correctly put the value in the correct bucket. Basically, if a Record Type of "Pol" and "End" have a matching Policy Number, then it looks at the Received Date to see if they are in the same year. If they are in the same year then it adds the "End" Total Exposure to the "Pol" Total Exposure and makes the "End" 0. If the "Pol" and "End" have a matching Policy Number but they are in different years, then "End" won't be added to the "Pol" Total Exposure and should be in the "16" bucket. 

In 2023, it has the sum, and every bucket correct. In 2024, it has the sum correct, but bucket 16 is too high and 1-15 is too low, by the same amount that 16 is too high. So it is not correctly moving those "End" Total Exposures into the right bucket.

The Effective_Total_Exposure DAX is: 

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 =

    CALCULATE(

        SUM('FactPolicyEndorsement'[NetExposure]),

        FILTER(

            'FactPolicyEndorsement',

            'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&

            'FactPolicyEndorsement'[RecordType] = "End" &&

            YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear

        )

    )

RETURN

IF(

    CurrentRecordType = "Pol",

    PolNetExposure + EndNetExposure,

    IF(

        CurrentRecordType = "End" &&

        CALCULATE(

            COUNTROWS('FactPolicyEndorsement'),

            FILTER(

                'FactPolicyEndorsement',

                'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&

                'FactPolicyEndorsement'[RecordType] = "Pol" &&

                YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear

            )

        ) > 0,

        0,

        'FactPolicyEndorsement'[NetExposure]

    )

)

The Key's Dax is: 

ExposureRangeKey = IF('FactPolicyEndorsement'[RecordType] = "End", 16, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= 0, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 1, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 2, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 3, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 4, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 5, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 6, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 7, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 8, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 9, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 10, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 11, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 12, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 13, IF(AND('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 'FactPolicyEndorsement'[Effective_Total_Exposure] < x), 14, IF('FactPolicyEndorsement'[Effective_Total_Exposure] >= x, 15, 0))))))))))))))))

[v-jialongy-msft · Accepted solution]

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.

[kellanbochenek]

Sample Data:

ReceivedDate: 12/31/2023, RecordType: Pol, PolicyNumber: 100, TotalExposure: $100

ReceivedDate: 1/1/2024, RecordType: End, PolicyNumber: 100, TotalExposure: $25

ReceivedDate: 6/1/2024, RecordType: Pol, PolicyNumber: 101, TotalExposure: $500

ReceivedDate: 7/1/2024, RecordType: End, PolicyNumber: 101, TotalExposure: $250

Expected Outcome for 1/1/2024-10/22/2024:

Exposure Range	Total Exposure
$0-$100	$0
$100-$1,000	$750
Endorsement received for Policy out of range	$25

[kellanbochenek]

Thought I'd add this.

Expected Outcome for 1/1/2023-12/31/2023:

Exposure Range	Total Exposure
$0-$99.99	$0
$100-$1,000	$100
Endorsement received for Policy out of range	$0

[v-jialongy-msft]

Thank you for your prompt reply!  lbendlin.

Hi @kellanbochenek 

Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

Best Regards,

Jayleny

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

[lbendlin]

That DAX seems incorrect. Where does x come from?

[kellanbochenek]

x is just a placeholder for different $ values and I am trying to put them in those different buckets.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...