Need help to determine DAX to solve the following problem

[bijaymaharjan]

I have 2 tables, a Claims table and a Date table. Claims table includes Claim Number, Loss amounts, Policy Effective Date and Transaction Effective Date. A Claim can have multiple Transaction Effective Date but only a single Policy Effective Date. Each Claim number is unique. The Claims table has an Active Relationship with Date table using Transaction Effective Date and an Inactive relationship using Policy Effective Date.

Requirements: 1. Need a measure to calculate the total loss based on the Policy Effective dates of the claims. The Power Bi will have an Transaction Date filter. When the filter gets applied, the Total Loss based on Policy Effective date needs to re-calculate so show the total based on the Transaction Date filter. Also, if the total loss of a claim is above 2M then the claim should be defaulted to 2M. The Power BI will have Policy Effective Date in Row, Total Loss as the values and Transaction Date as filter.

Example Table:

Claim_Number	Policy_Eff_Date	Transaction_Date	Total_Incurred
CPZ2500359	1/5/2025	2/19/2025	95000
CPZ2500359	1/5/2025	3/7/2025	0
CPZ2500359	1/5/2025	3/7/2025	0
CPZ2500359	1/5/2025	3/7/2025	0
CPZ2500359	1/5/2025	3/6/2025	3500
CPZ2500359	1/5/2025	4/24/2025	0
CPZ2500359	1/5/2025	4/23/2025	-1549.900024
CPZ2500359	1/5/2025	4/23/2025	1549.900024
CPZ2500359	1/5/2025	4/23/2025	40000
CPZ2500359	1/5/2025	4/23/2025	1416250
CPZ2500359	1/5/2025	4/23/2025	605700
CPZ2500359	1/5/2025	4/23/2025	730000
CPZ2500359	1/5/2025	5/5/2025	0
CPZ2500359	1/5/2025	5/20/2025	0
CPZ2500359	1/5/2025	5/23/2025	0
CPZ2500437	2/20/2025	3/31/2025	2500
CPZ2500437	2/20/2025	3/31/2025	10000
CPZ2500437	2/20/2025	4/25/2025	165000
CPZ2500437	2/20/2025	5/19/2025	0
CPZ2400239	1/16/2024	11/15/2024	20000
CPZ2400239	1/16/2024	11/15/2024	25000
CPZ2400239	1/16/2024	2/4/2025	0
CPZ2400239	1/16/2024	2/3/2025	25000
CPZ2400239	1/16/2024	4/30/2025	70000
CPZ2400239	1/16/2024	5/5/2025	0
CPZ2500360	1/20/2025	2/24/2025	17500
CPZ2500360	1/20/2025	3/21/2025	80000
CPZ2500360	1/20/2025	1/30/2025	3057.7

Q. I am going to have a Policy Year and then drill down option to show the month's in the year. In this example, say I have a Transaction filter set to 4/30/2025 then the table needs to filter out all the rows with Transaction Date greater than 4/30/2025 and then sum of the Total_Incurred values. One caveat is that if a total_incurred for a claim exceeds 2M then we only take 2M and then add up with the other claim to get the total incurred for the month. The sub-total or grand total can be more than 2M but not the individual claim total. In the following table, January has 2 policies CPZ2500359 and CPZ2500360. The total_incurred for CPZ2500359 will be 2890450 but since the total exceeded 2M we only take 2M. The total incurred for CPZ2500360 is 100557.7. Hence, the total for January 2025 is 2M + 100557.7 = 2100558. This is how I want to show data in a matrix:

Policy Year/Month	Total Incurred
2024	140000
January	140000
2025	2278058
January	2100558
February	177500

Similarly, if i change the filter to 2/28/2025 then all the rows with Transaction date after 2/28/2025 should be excluded. This is how the data should be when i select this filter:

Policy Year/Month	Total Incurred
2024	70000
January	70000
2025	112500
January	112500
February	0

[v-kpoloju-msft]

Hi @bijaymaharjan,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks @MarkLaf, @SamsonTruong, for those insights on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

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.

Thank you for using Microsoft Community Forum.

[bijaymaharjan]

Hi @v-kpoloju-msft ,
Thank you for looking in the problem and providing a solution for it but this is not correct. The Total_Incurred value you are showing in the matrix is total based on the Transaction date and not the Policy Effective Date. The date in the matrix should represent the Policy Year and the Total Incurred should be the total of the policy effective date. For example, if we set the filter to be from 11/15/2024 to 3/31/2025, then the table should filter to show all the data that has a transaction date between 11/15/2024 to 3/31/2025 and then the total should be done based on the Policy Effective date. I have included a detail example explanation in my requirements above. I hope you can help me out on this.

[bijaymaharjan]

Hi @SamsonTruong 
Thank you for the answer. Your query worked on getting the result but when I change the data in the filter the matrix does not get updated with the correct numbers. I have re-edited my original request with more clear examples and the desired result. Could you please go over the example description again and the update your query?

[MarkLaf]

If you are concerned about subtotals, then you'll want an iterator version:

Incurred_Capped2M = 
CALCULATE( 
    SUMX( 
        VALUES( 'Table'[Claim_Number] ), 
        MIN( CALCULATE( SUM( 'Table'[Total_Incurred] ) ), 2000000 ) 
    ), 
    Dates[Date] <= MAX( Dates[Date] ) 
)

If subtotals don't need to add up correctly (or if you want subtotals to also be capped at 2M):

Incurred_Capped2M_BadSubtotals = 
CALCULATE( 
    MIN( CALCULATE( SUM( 'Table'[Total_Incurred] ) ), 2000000 ),  
    Dates[Date] <= MAX( Dates[Date] ) 
)

In action (dates slicer is just showing end of month dates):

[bijaymaharjan]

Hi @MarkLaf ,
Thank you for looking at my question. The 2M cap does not apply to sub-total and grand-total, only to the individual claims total. I edited by original request with more detailed example and what I am looking for as a result. Could you please look at it again and revise your query to help me find the solution? 

[SamsonTruong]

Hi @bijaymaharjan ,

Please try using the following DAX measure and let me know if it achieves your desired result:

Total Loss:=
CALCULATE (
    SUMX (
        ADDCOLUMNS(
            VALUES(Claims[Claim_Number]),
            "ClaimLoss",
                MIN(2000000, 
                      CALCULATE(
                          SUM(Claims[Total_Incurred]),
                          ALLEXCEPT (Claims, Claims[Claim_Number])
                      )
                )
        ),
        [ClaimLoss]
    ),
    USERELATIONSHIP(Claims[Policy_Eff_Date], 'Date'[Date])
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session