- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Custom Column Only Works for Full Year
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:
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That DAX seems incorrect. Where does x come from?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
x is just a placeholder for different $ values and I am trying to put them in those different buckets.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-20-2024 08:14 AM | |||
Anonymous
| 05-05-2024 05:27 PM | ||
08-08-2024 10:34 PM | |||
03-16-2024 05:53 PM | |||
01-18-2024 06:48 PM |
User | Count |
---|---|
23 | |
12 | |
10 | |
9 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |