Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Date Slicer Filtering Issue

I have found the issue with my report, but am unable to fix the formula to work. Please see the formula below. The data in my BI goes back to 2019, but the actual data goes back much further. If the ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thank you for your help, but the formula does not work. I will paste below the formula I think is close. The issue with this formula is it makes all End 0 if there is a matching Pol from 2019-2025 (my entire dataset). The only End's that are not turned to 0 are those that have a matching Pol from 2018 or earlier, which isn't in my dataset. Even if I set the date slicer to 2024-2025, all End's are still turned to 0 even if the matching Pol is in 2023. 

    Effective Net Exposure =
    VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
    VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
    VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
    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 EndNetExposure_OutsideRange =
    CALCULATE(
        SUM('FactPolicyEndorsement'[NetExposure]),
        FILTER(
            'FactPolicyEndorsement',
            'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
            'FactPolicyEndorsement'[RecordType] = "End" &&
            ('FactPolicyEndorsement'[ReceivedDate] < MinDate || 'FactPolicyEndorsement'[ReceivedDate] > MaxDate)
        )
    )

    RETURN
    IF(
        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'[NetExposure]
        )
    )
v-sgandrathi's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous,

Thank you for your patience.

 

The workaround provided by DataNinja777  correctly addresses the issue you raised.

Specifically, the solution properly isolates the slicer's date context to avoid incorrect filtering, ensures "End" records return 0 only if a "Pol" record exists within the selected date range and correctly retains the Net Exposure for "End" records when no corresponding "Pol" exists in the range.

 

Kindly implement the suggested changes in your report. This should resolve the unexpected behavior you were encountering with the Date Slicer filtering.

 

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

 

Thank you for using Microsoft Fabric Community Forum.