Filter on calculated column- customers not bought

[mb0307]

Hi 

I want to find the customers who didnt buy in these category. 

"3 to 6 months"

"6 to 12 months"

"More then 12 Months"

" Recently"

I have made a calculated column , but its not showing the correct result. 1 customer should fall in 1category only and should latest 1 record.

But for me e.g. "A" customer is shown when i select "More then 12 Months" filter or " Recently" filter.

Trans
Customer	Inv Amount	Inv date	Current calculated Column Customer not Bought	Result sould be
A	100	03/01/2019	more then 12 months	Recently
A	200	20/06/2020	Recently	Recently
B	400	01/08/2019	more then 12 months	more then 12 months
C	200	06/05/2020	3 to 6 months	3 to 6 months
D	450	07/04/2020	3to 6 months	3to 6 months
E	300	12/09/2019	more then 12 months	3to 6 months
T	100	01/01/2020	6 to 12 months	6 to 12 months
D	300	15/02/2019	more then 12 months	3to 6 months
E	500	03/04/2020	3to 6 months	3to 6 months
R	100	08/07/2020	Recently	Recently

Measure

Last sale date = CALCULATE(MAXX(Trans[INV DATE],FILTERS(Cus[Customer]),ALL(Dates[Date]))

calculated column

Customer Not Bought =
VAR Mnth3 = DATE(YEAR(TODAY(),MONTH(TODAY())-3,DAY(TODAY()))
VAR Mnth6 = DATE(YEAR(TODAY(),MONTH(TODAY())-6,DAY(TODAY()))
VAR Mnth12 = DATE(YEAR(TODAY(),MONTH(TODAY())-12,DAY(TODAY()))
RETURN
IF(([Last Sale date] <=Mnth3 &&[Last Sale date] >=Mnth6),"3to6 Months",(IF([Last Sale date] <=Mnth6 &&[Last Sale date] >=Mnth12,"6to12 Months",,(IF([Last Sale date] <=Mnth12,"More then 12 Months", "Recently"))))

Can You please suggest what should I do to get this result .

Thanks for you help in advance.

[amitchandak]

@mb0307 , Try a new column with a date table in place join to date, else date from table

New column

Customer Not Bought =
VAR Mnth3 = CALCULATE(sumX(filter(Table,[Customer]=earlier([Customer])),Inv Amount),DATESINPERIOD('Date'[Date],today(),-3,MONTH))
VAR Mnth6 = CALCULATE(sumX(filter(Table,[Customer]=earlier([Customer])),Inv Amount),DATESINPERIOD('Date'[Date],today(),-6,MONTH))
VAR Mnth12 = CALCULATE(sumX(filter(Table,[Customer]=earlier([Customer])),Inv Amount),DATESINPERIOD('Date'[Date],today(),-12,MONTH))
RETURN
Switch ( True(),
not(isblank(Mnth3)) , "Recently",
not(isblank(Mnth6)) , "3 to 6 months",
not(isblank(Mnth3)) , "6 to 12 months",
"More then 12 Months"
)

Also, refer: new vs repeat customer
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/

[mb0307]

Hi Amit,

Thanks for your reply. Earlier function is not working, it gives error Earlier/earliest refres to an earlier row context which doesn't exist)

Is there any other fuction which i can use.

I have made a VAR ear =[Customer]

VAR Mnth3 = CALCULATE(sumX(filter(Table,[Customer]=ear),Inv Amount),DATESINPERIOD('Date'[Date],today(),-3,MONTH))

Then it gives a same result as earlier i.e.  customer falls in every category.

[v-xuding-msft · Accepted solution]

Hi @mb0307 ,

Please try this:

Column = 
VAR max_date =
    CALCULATE (
        MAX ( 'Table'[Inv date] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )
VAR diff =
    DATEDIFF ( max_date, TODAY (), MONTH )
RETURN
    SWITCH (
        TRUE (),
        diff < 3, "Recently",
        diff < 6, "3 to 6 Months",
        diff < 12, "6 to 12 Months",
        "More than 12 Months"
    )