Forum Discussion

kellanbochenek's avatar
kellanbochenek
Icon for Helper I rankHelper I
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 ...
  • kellanbochenek's avatar
    kellanbochenek
    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]
        )
    )
kellanbochenek's avatar
kellanbochenek
Icon for Helper I rankHelper I
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 kellanbochenek,

 

Thank you for sharing the formula and the detailed explanation of the issue. Based on your description, it seems that the behavior you're experiencing is not as expected, and it may require further investigation to identify the root cause. To ensure timely resolution and expert assistance, I recommend raising a support ticket to ensure we get the necessary assistance and resolution.
Looking forward to your confirmation.

Thank you.