Sum Fields for two different Date Ranges

[jenwho]

Hello!

I have a report that the user wants to see sums of the Gross Indemnity Paid and Gross Indemnity Reserve for two different date ranges based on the Claim Reported Date all in one table.

Scenario 1: SUM for each ClaimNumber from the beginning of the selected date month to the selected date (the slicer called Claim Reported EOM)

Scenario2: Sum for each PolicyNumber from the Policy Effective Date to the selected date (the slicer called Claim Reported EOM)

I have tried the following so far:

VAR SelectedDate = SELECTEDVALUE ( 'Calendar'[Date] )

VAR CalcExpr = CALCULATE (

    SUM ( 'P-QBE'[GrossIndemnityReserve]  ),

    'Country'[Country] = "United Kingdom",

    'Calendar'[Date] = SelectedDate,

    DATESBETWEEN ( 'Sales'[Sale Date], STARTOFMONTH ( VALUES ( 'Calendar'[Date] ) ), SelectedDate )

)

VAR SelectedDate =

SELECTEDVALUE( 'Calendar'[Date] )

VAR StartOfMonth =

EOMONTH( SelectedDate, -1 ) + 1

VAR Result =

CALCULATE(

SUM(  'P-QBE'[GrossIndemnityReserve] ),

DATESBETWEEN ( StartOfMonth, SelectedDate )

)

RETURN

Result

GrossIndemResITD =

VAR SelectedDate = MAX('Calendar'[Date])

RETURN

CALCULATE(

    SUM('P-QBE and VIP Combined'[GrossIndemnityReserve]),

    FILTER(

        'P-QBE and VIP Combined',

        'P-QBE and VIP Combined'[ReportDate] >= MAX('P-QBE and VIP Combined'[EffectiveDate]) &&

        'P-QBE and VIP Combined'[ReportDate] <= SelectedDate

    )

)

GrossIndemResMTD =

VAR SelectedDate = MAX('P-QBE and VIP Combined'[TransactionDate].[Date])

RETURN

CALCULATE(

    SUM('P-QBE and VIP Combined'[GrossIndemnityReserve]),

    FILTER(

        'P-QBE and VIP Combined',

        'P-QBE and VIP Combined'[ReportDate] >= STARTOFMONTH('Calendar'[Date]) &&

        'P-QBE and VIP Combined'[ReportDate] <= SelectedDate

    )

)

But nothing seems to work correctly. Thanks in advance!!

[danextian · Accepted solution]

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/1447... 

[v-tsaipranay]

Hi @jenwho ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @jenwho ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay · Accepted solution]

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.

[v-tsaipranay]

Hi @jenwho ,

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 my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[danextian · Accepted solution]

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/1447... 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.