Analytic Group by Customer based on their value

[Anno2019]

Hi Guru's

Need assistance please.  I am trying to create an Analytic Grouping to Class my Customers based on their profit over the last 12 months.

Below is the data structure and an example of the two columns I need highlighted in Green.

I need to create two columns;

1.  return the value of customer profit if the transaction was made within the last 12 months, if the date of transaction is not within the last 12 months then return 0.

2.  Give each customer a class based on their "last 12 month profit" spend.  I have included in my screenshot the formula as it would be done in excel.

=IF(E2>=1000,"AA",IF(AND(E2<1000,E2>=250),"A",IF(AND(E2<250,E2>=50),"B",IF(AND(E2<50,E2>=5),"C",IF(AND(E2<5,E2>=0.5),"D",IF(E2<0.5,"E","N/A"))))))

[v-frfei-msft]

Hi @Anno2019 ,

Based on your data, we can create two calculated column as below.

Profit for last 12 months = 
VAR last12month =
    TODAY () - 365
RETURN
    IF ( 'Table'[Date] >= last12month, 'Table'[Profit], BLANK () )

Customer Class = 
IF (
    ISBLANK ( 'Table'[Profit for last 12 months] ),
    "N/A",
    SWITCH (
        TRUE (),
        'Table'[Profit for last 12 months] >= 1000, "AA",
        'Table'[Profit for last 12 months] < 1000
            && 'Table'[Profit for last 12 months] >= 250, "A",
        'Table'[Profit for last 12 months] < 250
            && 'Table'[Profit for last 12 months] >= 50, "B",
        'Table'[Profit for last 12 months] < 50
            && 'Table'[Profit for last 12 months] >= 5, "C",
        'Table'[Profit for last 12 months] < 5
            && 'Table'[Profit for last 12 months] >= 0.5, "D",
        'Table'[Profit for last 12 months] < 0.5, "E"
    )
)

[Anno2019]

Hi @v-frfei-msft

Thank you so much for the assistance, it worked up to a point.  The minute the customer appears twice though it is not aggregating....

I have shared the link to my example file.  Customer Name is Duplicating.  Not sure how to fix.

https://www.dropbox.com/s/6un3qk9gkzbj7lw/Testing%20Shipment%20Data%20Customer%20Class.pbix?dl=0 

[v-frfei-msft · Accepted solution]

Hi @Anno2019 ,

To create a measure instead of a calculated column.

Customer Class 1 = 
VAR a =
    SUM ( 'Shipment Level Data'[Profit for last 12 months] )
RETURN
    IF (
        ISBLANK ( a ),
        "N/A",
        SWITCH (
            TRUE (),
            a >= 1000, "AA",
            a < 1000
                && a >= 250, "A",
            a < 250
                && a >= 50, "B",
            a < 50
                && a >= 5, "C",
            a < 5
                && a >= 0.5, "D",
            a < 0.5, "E"
        )
    )

[Anno2019]

Brilliant, exactly what I needed.