Sum Fields for two different Date Ranges

Hi jenwho 

Based on the sample formulas, it seems you are using different date columns while attempting to filter based on just one. If that's the case, you will need a separate dates table with one active relationship and several inactive ones. That said, please provide a working sample dataset (not an image), along with the expected results based on that dataset and your reasoning. You may also share a link to an Excel file or a sanitized copy of your PBIX file stored in the cloud.

Please refer to this post on how to get quicker answers - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Hi jenwho ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Based on your scenario, the issue you're facing stems from using multiple date columns (ClaimReportedDate, PolicyEffectiveDate) with a single slicer, which can cause filter context conflicts. As danextian correctly pointed out, you will need to handle multiple date relationships in your model.

Ensure you have a single Calendar table connected to your fact table (P-QBE) with one active relationship (e.g., ClaimReportedDate), One or more inactive relationships (e.g., PolicyEffectiveDate).

Scenario 1: Month-to-Date for ClaimNumber:

GrossIndemRes_MTD = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
VAR StartOfMonth = STARTOFMONTH(SelectedDate)
RETURN
CALCULATE(
    SUM('P-QBE'[GrossIndemnityReserve]),
    DATESBETWEEN('P-QBE'[ClaimReportedDate], StartOfMonth, SelectedDate)
)

Scenario 2: Inception-to-Date for PolicyNumber:

GrossIndemRes_ITD = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
    SUM('P-QBE'[GrossIndemnityReserve]),
    FILTER(
        'P-QBE',
        'P-QBE'[PolicyEffectiveDate] <= SelectedDate &&
        'P-QBE'[ClaimReportedDate] <= SelectedDate
    )
)

If PolicyEffectiveDate is not part of the active relationship, use USERELATIONSHIP() to activate it temporarily for the calculation.

If the issue still not solved yet, please share a sample dataset (Excel/PBIX) as mentioned by Ashish_Mathur  for further refinement.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.