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 Try with:
Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
-- Define the date range based on the slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
-- Define the period group for the current record's date (e.g., YYYYMM)
VAR CurrentPeriodGroup = FORMAT(CurrentReceivedDate, "YYYYMM")
-- Sum of 'End' records within the slicer range and grouped by PeriodGroup
VAR EndNetExposure_WithinRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)
-- Count of 'Pol' records within the slicer range and grouped by PeriodGroup
VAR PolCount_WithinRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)
RETURN
IF(
-- If the record type is "Pol", return its exposure + related "End" exposure within the same period
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,
IF(
-- If the record type is "End", check if a corresponding "Pol" exists in the same period within the slicer range
CurrentRecordType = "End" &&
PolCount_WithinRange > 0,
0, -- If there's a corresponding "Pol", set exposure to 0
'FactPolicyEndorsement'[TotalExposure] -- Otherwise, return the "End" exposure
)
)
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
It looks like this code you sent is the closest to working. The logic all seems good. This is correct, I am unsure why it is still off. Any other ideas? Thank you so much for the help.
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.
Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
-- Define the date range based on the slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
-- Define the period group for the current record's date (e.g., YYYYMM)
VAR CurrentPeriodGroup = FORMAT(CurrentReceivedDate, "YYYYMM")
-- Sum of 'End' records within the slicer range and grouped by PeriodGroup
VAR EndNetExposure_WithinRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)
-- Count of 'Pol' records within the slicer range and grouped by PeriodGroup
VAR PolCount_WithinRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)
RETURN
IF(
-- If the record type is "Pol", return its exposure + related "End" exposure within the same period
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,
IF(
-- If the record type is "End", check if a corresponding "Pol" exists in the same period within the slicer range
CurrentRecordType = "End" &&
PolCount_WithinRange > 0,
0, -- If there's a corresponding "Pol", set exposure to 0
'FactPolicyEndorsement'[TotalExposure] -- Otherwise, return the "End" exposure
)
)
The issue I have found with this formula is it leaves a slightly too high amount of "End" Total Exposure. The other formula you gave output a negative "End" Total Exposure which it should not be negative, so I think this formula is the closest if that helps.
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"
)
) > 0RETURN
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?
Effective Total Exposure =VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))VAR EndNetExposure_WithinRange =CALCULATE(SUM('FactPolicyEndorsement'[TotalExposure]),FILTER('FactPolicyEndorsement','FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&'FactPolicyEndorsement'[RecordType] = "End" &&'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&'FactPolicyEndorsement'[ReceivedDate] <= MaxDate))VAR EndNetExposure_OutsideRange =CALCULATE(SUM('FactPolicyEndorsement'[TotalExposure]),FILTER('FactPolicyEndorsement','FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&'FactPolicyEndorsement'[RecordType] = "End" &&('FactPolicyEndorsement'[ReceivedDate] < MinDate || 'FactPolicyEndorsement'[ReceivedDate] > MaxDate)))RETURNIF(CurrentRecordType = "Pol",PolNetExposure + EndNetExposure_WithinRange,IF(CurrentRecordType = "End" &&CALCULATE(COUNTROWS('FactPolicyEndorsement'),FILTER('FactPolicyEndorsement','FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&'FactPolicyEndorsement'[RecordType] = "Pol" &&'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&'FactPolicyEndorsement'[ReceivedDate] <= MaxDate)) > 0,0,'FactPolicyEndorsement'[TotalExposure]))