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.
I tried this formula too. It is basically my old formula but adding in the MIN and MAX dates as you suggested but same result.
For each record:
If it's a Pol, add its exposure plus all End exposures for the same PolicyNumber within the slicer date range.
If it's an End:
If a Pol exists for that same PolicyNumber within the same date range — suppress this End's exposure.
If no Pol exists for that PolicyNumber within the date range, keep the End's exposure as-is.
Correct?
Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
-- Slicer date range
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
-- Is there any 'Pol' for this PolicyNumber within the slicer range?
VAR HasPolInRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
) > 0
-- Total of End exposures for this PolicyNumber within slicer range
VAR EndExposureInRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
)
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!