Please help: Condition Column (has sample)

[XiananZhaoCSM]

Thank you for your time to read the post. I have some difficulties creating this condition column.  This report sample should list all customerIDs and weights shipped by Year/Month.

A condition column should indicates:

1. If current month's weight is blank or zero, then "Has Not Bought"

2. If current month's weight is less than previous month's AND previous month's weight is less than previous month+1's, then "Down 2 Months"

3. If current month's weight is less than previous month's AND previous month's weight is greater than previous month+1's, then "Down 1 Month"

4. Elses are "Good"

The goal is to use this condition column to filter.

It should be like this:

You can download the sample here

[BhaveshPatel]

You can download the SAMPLE FILE created for you.

[XiananZhaoCSM]

Thanks for the help. But the view is not what the result should look like. Based on your file, if I filter "Down 1 Month", customer 10016 shows. However, 10016's October weight is 4737 which is greater than September's weight 2759,54. The condition should be "Good"

[v-sihou-msft]

@XiananZhaoCSM

In this scenario, since you want this condition column appear in matrix, this column should repeat with each month in your raw data. For your requirement, you need to create calcualted column for "last Month Weight", "last second Month Weight" and "last third Month Weight". Then use those three column as condition to create the "condition" column. Please refer to my sample below:

I use my own sample table.

1. Create a calculated column to return the max month within each Name group.

max Month = CALCULATE(MAX(Table1[Month]),ALLEXCEPT(Table1,Table1[Name]))

2. Create three calculated columns for "last Month Weight", "last second Month Weight" and "last third Month Weight". 

last Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month],Table1[Name],Table1[Name])

last second Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month]-1,Table1[Name],Table1[Name])

last third Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month]-2,Table1[Name],Table1[Name])

3.  Create condition column based above three calculated columns.

Status = IF(Table1[last Month Value]=BLANK(),"Non",IF(Table1[last Month Value]<Table1[last second Month Value] && Table1[last second Month Value]<Table1[last third Month Value],"down 2",IF(Table1[last Month Value]<Table1[last second Month Value],"down 1","good")))

The table looks the like below:

When putting fields into matrix, it will appear as you expected.

[BhaveshPatel]

Instead of Conditional column, You can use SWITCH() function to return required results.

Here you will find more information on using SWITCH() in your scenario.