Forum Discussion
Multiple Years Filter
Please reference the DAX formula below. This formula works if the date slicer is within the same year, so 1/1/2024-12/31/2024 works. If the slicer is set to 2/1/2024-1/31/2025 it does not work because it spans over multiple years and the formula only works for one year. The issue is the End and Pol. If the End and Pol are within the same period, they should be added. But it is just looking at the Current Year, and not if the slicer spans over multiple years. Does anyone know how to adjust this to work over multiple years or reference the slicer dates? The date slicer is 'FactPolicyEndorsement'[ReceivedDate].
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.
18 Replies
kellanbochenek Hi! The fix is to stop relying on YEAR() and instead anchor your logic relative to the min and max dates in your slicer selection — which you can get using MIN() and MAX() over 'FactPolicyEndorsement'[ReceivedDate]. That way, your measure adapts to whatever range the slicer has applied.
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 PolCount_WithinRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
)RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,
IF(
CurrentRecordType = "End" && PolCount_WithinRange > 0,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Hello, thank you for your reply. The solution worked for some of the cases but not all, and I'm not sure why. I think it grouped the End and Pol in the same period correctly, but not the End that don't have a Pol in the same period. The first code I posted grouped these correctly. I can't figure out why the different date formula would mess with the groupings of End and Pol. Any thoughts? Thanks.
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
)
)
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.
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]))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!
Hi kellanbochenek ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi kellanbochenek ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hello, I reviewed all formulas given, but none have been able to correctly solve the issue.
