Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
scabral
Helper IV
Helper IV

get values at certain time interval and sum

Hi,

 

i have a table called Payments that stores all payments made for a claim and the date the payments were made.  I also have a claim table that stores the date the claim was reported.

 

what i need to do is come up with a way to get the payment for each claim 60 days after the claim reported date and then sum those payments up.

 

so for example if i have the following 2 claims in the claim table:

 

claim iddate reported
1234501-01-2020
456783-1-2020

 

and i have the payments for each claim stored in a payments table:

claim idpayment amtpayment date
123451001-10-2020
123455002-4-2020
456783503-1-2020
456784504-1-2020
456787506-1-2020

 

so for claim id 12345 i would want the 500 payment since that is the latest payment made before the 60 day from date reported.  for the 45678 claim i would want the 450 payment since that is the latest payment made before the 60 day from date of loss. so in the end i would want to add them up and come out with a measure that shows 950.

 

Scott

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @scabral ,

 

Try this measure:

TT_Amount = 
SUMX(
        SUMMARIZE(
                Payment, 
                Claim[claim id], 
                Claim[date reported], 
                "Last_Amount_60D",  
                                VAR _lastDate60D = CALCULATE(MAX(Payment[payment date]), FILTER(ALL(Payment[payment date]), Payment[payment date] <= Claim[date reported] + 60))
                                RETURN CALCULATE(SUM(Payment[payment amt]), FILTER(ALL(Payment[payment date]), Payment[payment date] = _lastDate60D))
        ),
    [Last_Amount_60D]
)

 

Capture.PNG



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

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

Hi @scabral ,

 

Try this measure:

TT_Amount = 
SUMX(
        SUMMARIZE(
                Payment, 
                Claim[claim id], 
                Claim[date reported], 
                "Last_Amount_60D",  
                                VAR _lastDate60D = CALCULATE(MAX(Payment[payment date]), FILTER(ALL(Payment[payment date]), Payment[payment date] <= Claim[date reported] + 60))
                                RETURN CALCULATE(SUM(Payment[payment amt]), FILTER(ALL(Payment[payment date]), Payment[payment date] = _lastDate60D))
        ),
    [Last_Amount_60D]
)

 

Capture.PNG



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

Proud to be a Super User!



Hi,

below is my exact query with the acutal table names, but i'm getting the following error on the date field + 60 (coming from the date table):

"A single value for column 'Date Reported Calendar Date' in table 'Date Reported' cannot be determined."

 

SUMX(
    SUMMARIZE (
        FILTER (
            'Claim Reserve Values',
            RELATED ( 'Claim Adjustment File'[Claim Number] ) = "414000"
        ),
        'Claim Reserve Values'[Claim ID],
        'Claim Reserve Values'[Location ID],
        'Claim Reserve Values'[Policy ID],
        'Claim Reserve Values'[Damage ID],
        'Claim Reserve'[Reserve Peril ID],
        "60 Day Reserve Amt",
            VAR _lastDate60D =
                CALCULATE (
                    MAX ( 'Claim Reserve Date'[Claim Reserve Calendar Date] ),
                    FILTER (
                        ALL ( 'Claim Reserve Date'[Claim Reserve Calendar Date] ),
                        'Claim Reserve Date'[Claim Reserve Calendar Date] <= 'Date Reported'[Date Reported Calendar Date] + 60
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Claim Reserve Values'[100 % Gross Amount Reported] ),
                    FILTER (
                        ALL ( 'Claim Reserve Date'[Claim Reserve Calendar Date] ),
                        'Claim Reserve Date'[Claim Reserve Calendar Date] = _lastDate60D
                    )
                )
    ),
    [60 Day Reserve Amt]
)

Hi @scabral ,

 

You are missing the 'Date Reported'[Date Reported Calendar Date]  in the group for summarize function, so you can use it later.

 



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

Proud to be a Super User!



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.