get values at certain time interval and sum

[scabral]

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 id	date reported
12345	01-01-2020
45678	3-1-2020

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

claim id	payment amt	payment date
12345	100	1-10-2020
12345	500	2-4-2020
45678	350	3-1-2020
45678	450	4-1-2020
45678	750	6-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

[camargos88 · Accepted solution]

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

[scabral]

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

[camargos88]

Hi @scabral ,

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