Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

First and last dates and values

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 CODEDATEDISCOUNT
A0511/1/20220.2
A0516/1/20220.1
A25112/1/20210.2
B10012/1/20210.05
B1006/1/20220.1
B10111/1/20210.03
B1019/1/20220.08
B10612/1/20210.25
B1065/1/20220.3
B11110/1/20210.1
B1113/1/20220.05
C2133/1/20220.15
C51612/1/20210.25
C5165/1/20220.15

 

Here is the result I looking for:

CUSTOMER CODELAST DATEFIRST DISCOUNTLAST DISCOUNT
A0516/1/20220.20.1
A25112/1/20210.20.2
B1006/1/20220.050.1
B1019/1/20220.030.08
B1065/1/20220.250.3
B1113/1/20220.10.05
C2133/1/20220.150.15
C5165/1/20220.250.15

 

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

 

Thank you in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • ddpl's avatar
    ddpl
    Solution Sage
    4 years ago

    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])))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.