Date Slicer Filtering Issue

Hi kellanbochenek,

Thank you for your continued clarification, and I understand the issue more clearly now. The error you're encountering is due to the format of the ReceivedDate field, as it contains a full date (MM/DD/YYYY), not just the year. To resolve this and ensure the formula handles multiple years dynamically, we'll need to make sure we correctly extract the year from the ReceivedDate field.

Here’s a refined version of the formula that addresses both the date format issue and ensures that the End records are correctly zeroed out when there is a matching Pol within the selected date range. It will also preserve the dynamic handling of multiple years from the slicer:

Effective Net Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[NetExposure]

VAR SelectedYears =
    VALUES(YEAR('FactPolicyEndorsement'[ReceivedDate])) // Extract the Year from the full ReceivedDate

VAR EndNetExposure_SameYear =
    CALCULATE(
        SUM('FactPolicyEndorsement'[NetExposure]),
        FILTER(
            ALL('FactPolicyEndorsement'),
            'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
            'FactPolicyEndorsement'[RecordType] = "End" &&
            YEAR('FactPolicyEndorsement'[ReceivedDate]) IN SelectedYears
        )
    )

VAR HasPolInSelectedYears =
    CALCULATE(
        COUNTROWS('FactPolicyEndorsement'),
        FILTER(
            ALL('FactPolicyEndorsement'),
            'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
            'FactPolicyEndorsement'[RecordType] = "Pol" &&
            YEAR('FactPolicyEndorsement'[ReceivedDate]) IN SelectedYears
        )
    )

RETURN
    IF(
        CurrentRecordType = "Pol",
        PolNetExposure + EndNetExposure_SameYear,
        IF(
            CurrentRecordType = "End" && HasPolInSelectedYears > 0,
            0, // Zero out 'End' if a matching 'Pol' exists within the selected years
            'FactPolicyEndorsement'[NetExposure] // Otherwise, retain the NetExposure
        )
    )

The formula has been updated to properly handle the ReceivedDate by extracting the year using the YEAR() function, ensuring it works with the slicer and supports dynamic filtering across multiple years. The SelectedYears variable now correctly stores the selected years from the slicer, so the formula adapts based on those settings. Additionally, the matching logic for "Pol" and "End" records has been adjusted to ensure that "End" records are only zeroed out if there is a corresponding "Pol" within the selected date range.

Please update your report with this revised formula and test it again with different date slicer settings. The solution should now correctly zero out "End" records when there’s a matching "Pol" within the selected range and retain the correct NetExposure for other cases. If you face any further issues, feel free to reach out.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Thank you.