Forum Discussion
Helper ICustom 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:
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.
- 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.
7 Replies
- lbendlin
Super User
That DAX seems incorrect. Where does x come from?
kellanbochenekx 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-Forum/ba-p/963216
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/1447523
Thank you for your prompt reply! lbendlin.
Hi kellanbochenekPlease 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.
- 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
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.
- 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.
