topic get values at certain time interval and sum in DAX Commands and Tips

get values at certain time interval and sum

scabral — Fri, 18 Sep 2020 14:39:20 GMT

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

Re: get values at certain time interval and sum

camargos88 — Fri, 18 Sep 2020 18:00:53 GMT

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

Re: get values at certain time interval and sum

scabral — Sat, 19 Sep 2020 00:23:43 GMT

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

Re: get values at certain time interval and sum

camargos88 — Sat, 19 Sep 2020 20:49:04 GMT

Hi scabral ,

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