Date Slicer Filtering Issue

Hi kellanbochenek ,

You're working with a DAX formula that calculates Effective Net Exposure based on two record types in your dataset: "Pol" (policy issuance) and "End" (endorsements). Your model includes a slicer filtering 'FactPolicyEndorsement'[ReceivedDate] — let’s say it's set to only show data for the year 2024.

The logic you want is:

• For "Pol" records: return the Net Exposure of that row plus any associated "End" Net Exposure that falls within the slicer's selected date range.
• For "End" records: if there is a "Pol" record within the selected date range for the same PolicyNumber, then return 0. Otherwise, return the NetExposure for the "End" row as-is.

Sounds simple enough — but the issue is that the current measure incorrectly zeroes out "End" exposures when there’s any matching "Pol" record in the dataset, even if it’s outside the selected date range. In your case, it’s returning 0 for "End" rows in 2024 just because there’s a "Pol" for that policy from 2019 still hanging around.

The root cause? Your original CALCULATE logic is checking for a "Pol" match in a way that inherits unwanted filter context from the report. Specifically, your formula didn’t fully isolate the slicer’s date context, so it was evaluating the condition over the entire table (from 2019 to 2025).

The fix? You need to remove all filters from the table, then reapply only the filters you care about: same PolicyNumber, RecordType = "Pol", and the received date falling within the selected range.

Here is the fully rewritten, clean, copy-pasteable DAX measure that will behave correctly:

Effective Net Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR PolNetExposure = 'FactPolicyEndorsement'[NetExposure]
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

VAR EndNetExposure_WithinRange =
    CALCULATE(
        SUM('FactPolicyEndorsement'[NetExposure]),
        FILTER(
            'FactPolicyEndorsement',
            'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
            'FactPolicyEndorsement'[RecordType] = "End" &&
            'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
            'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
        )
    )

VAR HasPolInDateRange =
    CALCULATE(
        COUNTROWS('FactPolicyEndorsement'),
        REMOVEFILTERS('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" && HasPolInDateRange > 0,
            0,
            'FactPolicyEndorsement'[NetExposure]
        )
    )

This version uses REMOVEFILTERS to make sure you're not unintentionally applying filters from other visuals or fields that might mess with your logic. The measure strictly follows the behavior you want based on the slicer’s date range and the relationship between "Pol" and "End" records.

Let me know if you want to add even more precision — like ignoring future dates, treating overlapping policies differently, or warning if no "Pol" exists at all.

Best regards,