Multiple Years Filter

[kellanbochenek]

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 because it spans over multiple years and the formula only works for one year. The issue is the End and Pol. If the End and Pol are within the same period, they should be added. But it is just looking at the Current Year, and not if the slicer spans over multiple years. Does anyone know how to adjust this to work over multiple years or reference the slicer dates? The date slicer is 'FactPolicyEndorsement'[ReceivedDate].

Effective Total Exposure =

VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]

VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]

VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]

VAR CurrentYear = YEAR(CurrentReceivedDate)

VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

VAR EndNetExposure_SameYear =

CALCULATE(

SUM('FactPolicyEndorsement'[TotalExposure]),

FILTER(

'FactPolicyEndorsement',

'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&

'FactPolicyEndorsement'[RecordType] = "End" &&

YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear

)

)

VAR EndNetExposure_DiffYear =

CALCULATE(

SUM('FactPolicyEndorsement'[TotalExposure]),

FILTER(

'FactPolicyEndorsement',

'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&

'FactPolicyEndorsement'[RecordType] = "End" &&

YEAR('FactPolicyEndorsement'[ReceivedDate]) <> CurrentYear

)

)

RETURN

IF(

CurrentRecordType = "Pol",

PolNetExposure + EndNetExposure_SameYear,

IF(

CurrentRecordType = "End" &&

CALCULATE(

COUNTROWS('FactPolicyEndorsement'),

FILTER(

'FactPolicyEndorsement',

'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&

'FactPolicyEndorsement'[RecordType] = "Pol" &&

YEAR('FactPolicyEndorsement'[ReceivedDate]) = CurrentYear

)

) > 0,

0,

'FactPolicyEndorsement'[TotalExposure]

)

)

[v-menakakota]

Hi @kellanbochenek ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @kellanbochenek ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[kellanbochenek]

Hello, I reviewed all formulas given, but none have been able to correctly solve the issue.

[v-menakakota]

Hi @kellanbochenek ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.

You need to ensure the Pol lookup is also restricted to the slicer date range, so that you're only checking for  Pol records within that selected range.

Once try with this measure:

Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

-- Date range from slicer
VAR MinDate =
    CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate =
    CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

-- All records for the same policy in the slicer range
VAR RecordsForPolicyInRange =
    FILTER(
        ALL('FactPolicyEndorsement'),
        'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
        'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
        'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
    )

-- Total exposure from End records in range
VAR EndExposureInRange =
    CALCULATE(
        SUM('FactPolicyEndorsement'[TotalExposure]),
        FILTER(RecordsForPolicyInRange, 'FactPolicyEndorsement'[RecordType] = "End")
    )

-- Check if there's a Pol in the same range
VAR HasPolInRange =
    CALCULATE(
        COUNTROWS(RecordsForPolicyInRange),
        FILTER(RecordsForPolicyInRange, 'FactPolicyEndorsement'[RecordType] = "Pol")
    ) > 0

RETURN
IF(
    CurrentRecordType = "Pol",
    PolNetExposure + EndExposureInRange,
    IF(
        CurrentRecordType = "End" && HasPolInRange,
        0,
        'FactPolicyEndorsement'[TotalExposure]
    )
)

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

[v-menakakota]

Hi @kellanbochenek ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[kellanbochenek]

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 · Accepted solution]

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.

[v-menakakota]

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-menakakota]

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.

 
Best Regards, 
Menaka.
Community Support Team 

[kellanbochenek]

I tried this formula too. It is basically my old formula but adding in the MIN and MAX dates as you suggested but same result.

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]

    )

)

[BeaBF]

@kellanbochenek 

For each record:

If it's a Pol, add its exposure plus all End exposures for the same PolicyNumber within the slicer date range.

If it's an End:

If a Pol exists for that same PolicyNumber within the same date range — suppress this End's exposure.

If no Pol exists for that PolicyNumber within the date range, keep the End's exposure as-is.

Correct?

Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

-- Slicer date range
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

-- Is there any 'Pol' for this PolicyNumber within the slicer range?
VAR HasPolInRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
) > 0

-- Total of End exposures for this PolicyNumber within slicer range
VAR EndExposureInRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)
)

RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndExposureInRange,
IF(
CurrentRecordType = "End" && HasPolInRange,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[kellanbochenek]

Hello, thank you for your reply. The solution worked for some of the cases but not all, and I'm not sure why. I think it grouped the End and Pol in the same period correctly, but not the End that don't have a Pol in the same period. The first code I posted grouped these correctly. I can't figure out why the different date formula would mess with the groupings of End and Pol. Any thoughts? Thanks.

[BeaBF]

@kellanbochenek Try with:

Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

-- Define the date range based on the slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

-- Define the period group for the current record's date (e.g., YYYYMM)
VAR CurrentPeriodGroup = FORMAT(CurrentReceivedDate, "YYYYMM")

-- Sum of 'End' records within the slicer range and grouped by PeriodGroup
VAR EndNetExposure_WithinRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)

-- Count of 'Pol' records within the slicer range and grouped by PeriodGroup
VAR PolCount_WithinRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)

RETURN
IF(
-- If the record type is "Pol", return its exposure + related "End" exposure within the same period
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,

IF(
-- If the record type is "End", check if a corresponding "Pol" exists in the same period within the slicer range
CurrentRecordType = "End" &&
PolCount_WithinRange > 0,
0, -- If there's a corresponding "Pol", set exposure to 0
'FactPolicyEndorsement'[TotalExposure] -- Otherwise, return the "End" exposure
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[kellanbochenek]

It looks like this code you sent is the closest to working. The logic all seems good. This is correct, I am unsure why it is still off. Any other ideas? Thank you so much for the help.

If it's a Pol, add its exposure plus all End exposures for the same PolicyNumber within the slicer date range.

If it's an End:

If a Pol exists for that same PolicyNumber within the same date range — suppress this End's exposure.

If no Pol exists for that PolicyNumber within the date range, keep the End's exposure as-is.

Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR CurrentReceivedDate = 'FactPolicyEndorsement'[ReceivedDate]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

-- Define the date range based on the slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

-- Define the period group for the current record's date (e.g., YYYYMM)
VAR CurrentPeriodGroup = FORMAT(CurrentReceivedDate, "YYYYMM")

-- Sum of 'End' records within the slicer range and grouped by PeriodGroup
VAR EndNetExposure_WithinRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "End" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)

-- Count of 'Pol' records within the slicer range and grouped by PeriodGroup
VAR PolCount_WithinRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
'FactPolicyEndorsement',
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[RecordType] = "Pol" &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate &&
FORMAT('FactPolicyEndorsement'[ReceivedDate], "YYYYMM") = CurrentPeriodGroup
)
)

RETURN
IF(
-- If the record type is "Pol", return its exposure + related "End" exposure within the same period
CurrentRecordType = "Pol",
PolNetExposure + EndNetExposure_WithinRange,

IF(
-- If the record type is "End", check if a corresponding "Pol" exists in the same period within the slicer range
CurrentRecordType = "End" &&
PolCount_WithinRange > 0,
0, -- If there's a corresponding "Pol", set exposure to 0
'FactPolicyEndorsement'[TotalExposure] -- Otherwise, return the "End" exposure
)
)

[kellanbochenek]

The issue I have found with this formula is it leaves a slightly too high amount of "End" Total Exposure. The other formula you gave output a negative "End" Total Exposure which it should not be negative, so I think this formula is the closest if that helps.

[BeaBF]

@kellanbochenek  it's a little difficult without data, try with:

Effective Total Exposure =
VAR CurrentPolicyNumber = 'FactPolicyEndorsement'[PolicyNumber]
VAR CurrentRecordType = 'FactPolicyEndorsement'[RecordType]
VAR PolNetExposure = 'FactPolicyEndorsement'[TotalExposure]

-- Date range from slicer
VAR MinDate = CALCULATE(MIN('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))
VAR MaxDate = CALCULATE(MAX('FactPolicyEndorsement'[ReceivedDate]), ALLSELECTED('FactPolicyEndorsement'))

-- Filtered table of all records for this PolicyNumber within the slicer range
VAR RecordsForPolicyInRange =
FILTER(
ALL('FactPolicyEndorsement'),
'FactPolicyEndorsement'[PolicyNumber] = CurrentPolicyNumber &&
'FactPolicyEndorsement'[ReceivedDate] >= MinDate &&
'FactPolicyEndorsement'[ReceivedDate] <= MaxDate
)

-- Total End exposure for this PolicyNumber within the slicer range
VAR EndExposureInRange =
CALCULATE(
SUM('FactPolicyEndorsement'[TotalExposure]),
FILTER(
RecordsForPolicyInRange,
'FactPolicyEndorsement'[RecordType] = "End"
)
)

-- Is there a Pol for this PolicyNumber in the slicer range?
VAR HasPolInRange =
CALCULATE(
COUNTROWS('FactPolicyEndorsement'),
FILTER(
RecordsForPolicyInRange,
'FactPolicyEndorsement'[RecordType] = "Pol"
)
) > 0

RETURN
IF(
CurrentRecordType = "Pol",
PolNetExposure + EndExposureInRange,
IF(
CurrentRecordType = "End" && HasPolInRange,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[kellanbochenek]

See below for the current code I am using. I think I found the issue. My data in the BI goes back to 2019, but in reality, it goes back much further. The issue is that if the End has a matching Pol PolicyNumber from 2019-2025 it turns it to 0, regardless of what the slicer is set to. But if the End has a matching Pol PolicyNumber prior to 2019, it keeps it as is. So when I set the date range to 2024, it still turns those End to 0, even if the matching Pol was in 2023. Any thoughts?

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]

    )

)

[BeaBF]

@kellanbochenek Hi! The fix is to stop relying on YEAR() and instead anchor your logic relative to the min and max dates in your slicer selection — which you can get using MIN() and MAX() over 'FactPolicyEndorsement'[ReceivedDate]. That way, your measure adapts to whatever range the slicer has applied.

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 PolCount_WithinRange =
CALCULATE(
COUNTROWS('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" && PolCount_WithinRange > 0,
0,
'FactPolicyEndorsement'[TotalExposure]
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!