Forum Discussion

kellanbochenek's avatar
kellanbochenek
Helper 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]
        )
    )
v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

HI kellanbochenek,

 

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

Hi kellanbochenek,

 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

 

Thank you for your understanding and participation.