Condition filter based on values in current month and previous months

[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 weight] is blank or zero, then "Has Not Bought"

2. If [current month weight] < [previous month weight] AND [previous month weight] < [previous previous month weight] , then "Down 2 Months"

3. If [current month weight] < [previous month weight] AND [previous month weight] > [previous previous month weight], 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

[CheenuSing · Accepted solution]

Hi @XiananZhaoCSM

I managed to find a solution. Please follow the steps.

1. Create a Calendar Table as 

       Calendar = CALENDAR (MINX('Main', [InvoiceDate]), MAXX('Main', [InvoiceDate]))

    This creates continuous dates from min to max of the Invoicedate in Main table.

2. Create a  measure to find the currentmonth based on the max invoicedate using

    CurMonth = Month(Max('Calendar'[Date]))

3. Create a column called Month in Main Table as Month = Format(Main[InvoiceDate],"MMMM")

4. Create a column called MonthNo in Main Table as MonthNo = Month(Main[InvoiceDate])

5. Set the Month Column to be sorted by MonthNo

6. Create a summary table called CustWeights as

    CustWeights =           SUMMARIZE(Main,Main[Customer],Main[Month],"MonthNo",distinct(Main[MonthNo]),"TotWeight",sum(Main[Weight]))

7. Under the CustWeights table Create a measure called CurMonthWeight as

        CurMonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]))

8. Under the CustWeights table Create a measure called PreviousMonthWeight as

      PreviousMonthWeight = Calculate(sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth] -1 ))

9. Under the CustWeights table Create a measure called Previous2MonthWeight as

        Previous2MonthWeight = Calculate(Sum([TotWeight]),Filter(CustWeights ,CustWeights [MonthNo]=[CurMonth]-2))

10.  Create another summarized table called CustLastBought as

   CustLastBought= SUMMARIZE(CustWeights,CustWeights[Customer],"LastTransacted",Max(CustWeights[MonthNo]))

11. Add a column in this table

    Description = If([LastTransacted]<>[CurMonth],"Has not Bought",
                                                           If([CurMonthWeight]<[PreviousMonthWeight] &&
                                                                  [PreviousMonthWeight] < [Previous2MonthWeight] ,"DownTwoMonths" ,

                                                                             If([CurMonthWeight]<[PreviousMonthWeight] &&
                                                                           [PreviousMonthWeight] > [Previous2MonthWeight] ,"Down 1 Months" ,
                                                                                 "Good")
  14. Using Manage Relationships create the relationship between tables.

 13. Now for the last Part

       Create a Matrix table visual

       With Customer from CustLastBought Table as the Row,

       With Description from CustLastBought Table as the Row,

        With Month from CustWeights as Columns,

        With TotWeight from CustWeights as Values

and you should be able to see the results.

        

If this solves your issue please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[XiananZhaoCSM]

Got it right now. I changed first IF clause to IF([LastTransacted) < Max(CustWeight[MonthNo]), "Has Not Bought", .......

I don't know why yours is not working. Your LastTransacted value and CurMonth value are both 10. It should be good. Thank you very much.

[XiananZhaoCSM]

Never mind about the last post. I figured it out. But the description is still showing "Good" on those rows that suppose to be "Has Not Bought". I will keep tweaking. Thanks alot.

[XiananZhaoCSM]

@CheenuSing

Thank you for your time and effort. In your step 11, the first "IF" clause, why do you write [LastTransacted] <> [CurMonth], "Has Not Bought"? I ask this because the result is not showing any "Has Not Bought". All the other IF clause are comparing weight, but the first one is comparing month number. Is this a typo or is intended? Thank you.