First and last dates and values

[Anonymous]

Greetings,

I need to help to identify the first and last date and values in a table.  Min and Max won't work because the values are a combination of increasing and decreasing amounts.

Here is a sample of the table:

CUSTOMER CODE	DATE	DISCOUNT
A051	1/1/2022	0.2
A051	6/1/2022	0.1
A251	12/1/2021	0.2
B100	12/1/2021	0.05
B100	6/1/2022	0.1
B101	11/1/2021	0.03
B101	9/1/2022	0.08
B106	12/1/2021	0.25
B106	5/1/2022	0.3
B111	10/1/2021	0.1
B111	3/1/2022	0.05
C213	3/1/2022	0.15
C516	12/1/2021	0.25
C516	5/1/2022	0.15

Here is the result I looking for:

CUSTOMER CODE	LAST DATE	FIRST DISCOUNT	LAST DISCOUNT
A051	6/1/2022	0.2	0.1
A251	12/1/2021	0.2	0.2
B100	6/1/2022	0.05	0.1
B101	9/1/2022	0.03	0.08
B106	5/1/2022	0.25	0.3
B111	3/1/2022	0.1	0.05
C213	3/1/2022	0.15	0.15
C516	5/1/2022	0.25	0.15

Note: Some of the customers have only one entry, therefore, the first/last discount will be the same value.

Thank you in advance!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Please try measures as below to create a table visual.

First Discount =
VAR _STARTDATE =
    CALCULATE (
        MIN ( 'Table'[DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[CUSTOMER CODE] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[DISCOUNT] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CUSTOMER CODE] ),
            'Table'[DATE] = _STARTDATE
        )
    )

Last Discount =
VAR _EndDATE =
    CALCULATE (
        MAX ( 'Table'[DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[CUSTOMER CODE] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[DISCOUNT] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CUSTOMER CODE] ),
            'Table'[DATE] = _EndDATE
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ddpl]

@Anonymous 

Hey, Create a new calculated table as per below...

Your Need = 
SUMMARIZE(
   'Table','Table'[CUSTOMER CODE], 
   "Last Date", MAX('Table'[DATE]), 
   "1st Disc", CALCULATE(MAX('Table'[DISCOUNT]),'Table'[DATE]=MIN('Table'[DATE])), 
   "Last Disc", CALCULATE(MAX('Table'[DISCOUNT]),'Table'[DATE]=MAX('Table'[DATE])))