Forum Discussion
Multiple Years Filter
Hi kellanbochenek ,
We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.Please raise support ticket to resolve the issue,to raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Thank you for being a part of Microsoft Fabric Community Forum.
kellanbochenek it's a little difficult without data, try with:
Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
-- Date range from slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
-- Filtered table of all records for this PolicyNumber within the slicer range
VAR RecordsForPolicyInRange =
FILTER(
ALL('FactPolicyEndorsement'),
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
-- Total End exposure for this PolicyNumber within the slicer range
VAR EndExposureInRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
RecordsForPolicyInRange,
'FactPolicyEndorsement'[RecordType] = "End"
)
)
-- Is there a Pol for this PolicyNumber in the slicer range?
VAR HasPolInRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
RecordsForPolicyInRange,
'FactPolicyEndorsement'[RecordType] = "Pol"
)
) > 0
RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndExposureInRange,
IF(
CurrentRecordType = "End" && HasPolInRange,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
See below for the current code I am using. I think I found the issue. My data in the BI goes back to 2019, but in reality, it goes back much further. The issue is that if the End has a matching Pol PolicyNumber from 2019-2025 it turns it to 0, regardless of what the slicer is set to. But if the End has a matching Pol PolicyNumber prior to 2019, it keeps it as is. So when I set the date range to 2024, it still turns those End to 0, even if the matching Pol was in 2023. Any thoughts?