Forum Discussion

kellanbochenek's avatar
kellanbochenek
Icon for Helper I rankHelper I
1 year ago
Solved

Multiple Years Filter

Please reference the DAX formula below. This formula works if the date slicer is within the same year, so 1/1/2024-12/31/2024 works. If the slicer is set to 2/1/2024-1/31/2025 it does not work becaus...
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi kellanbochenek ,

    We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.

    Please raise support ticket to resolve the issue,to raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

    How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

     

    If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

    Thank you for being a part of Microsoft Fabric Community Forum.

     

     

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 Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

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

VAR EndNetExposure_OutsideRange =
CALCULATE(
    SUM('FactPolicyEndorsement'[TotalExposure]),
    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'[TotalExposure]
    )
)
v-menakakota's avatar
v-menakakota
Icon for Community Support rankCommunity Support
1 year ago

Hi kellanbochenek ,

We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.

Please raise support ticket to resolve the issue,to raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

 

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Thank you for being a part of Microsoft Fabric Community Forum.