show the list of customers who do 0 orders in selected month and do order in previous month.

[Lil]

Dear Community,

I am new to BI and need your help.

I have 3 tables for years 2022 and 2023

"Order table " with columns (Customer ID, order amount, order ID, order Date),

"Date Table " with columns (date, year, month, Year Month, week No, ....) and

"Customer table" with columns (Customer ID, Customer name, Customer Phone).

relationship 1 to many for "Date Table " (date) and "Order table " (order date)   

relationship 1 to many for "Customer table"(Customer ID) with "Order Table " (Customer ID)

I make slicer of Year Month column from Date table.  

And create measure MTD and LMTD , total amount.

total amont = SUM('Order table'[AMOUNT])

MTD = calculate ([total amount],datesmtd('DateTable'[Date]))

LMTD = calculate ([total amount],DATESMTD(DATEADD('DateTable'[Date],-1,MONTH)))   

when I chose Year Month from slicer,

I need to count and show the list of customers with 4 options (Button).

1. Active Customer - (who make order in selected month and previous month)

             Active Customer ++ = Sumx(VALUES('Order table'[CUSTOMER_ID]),if(not(ISBLANK([MTD])) && (not(ISBLANK([LMTD]))) , 1,BLANK()))

1. Passive Customer - (who make 0 order in selected month and make order in previous month)

              Passive Customer +- = Sumx(VALUES('Order table'[CUSTOMER_ID]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))

1. New Customer - (who make order in selected month and make 0 order in previous month)

           New Customer -+ = sumx(VALUES(Order table[CUSTOMER_ID]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))

1. Lost Customer - (who make 0 order in selected month and previous month)

             Lost Customer-- = Sumx(VALUES('Order table'[CUSTOMER_ID]),if(ISBLANK([MTD]) && (ISBLANK([LMTD])) , 1,BLANK()))

 but it seems DAX  is not working for option 2 and 4.

Order table

Client ID	Amount	Order date	Order ID
123	600	April 22	35
123	700	May 22	4
123	100	April 22	39
123	700	June 22	85
213	650	July 22	21
422	200	April 22	47
422	2500	May 22	23
422	2500	August22	11
336	200	April 22	47
336	2500	May 22	23
336	2500	August22	11

Slicer

Jan 22

Feb 22

Mar 22

Apr 22

May 22

June 22

For example:

Result for option 1

select May 2022 in slicer and table should show the active customers (result for April, May)

Client ID	Phone	Name	Active Customers
123	5465465	Ann	1
422	2562568	Tom	1
336	1122333	David	1
Total			3

Result for option 2

select June 2022 in slicer and table should show the passive customers (result for May, June)

Client ID	Phone	Name	Passive Customers
123	5465465	Ann	1
422	2562568	Tom	1
336	1122333	David	1
Total			3

Result for option 4

select March 2022 in slicer and table should show the Lost Customers (result for February, March)

Client ID	Phone	Name	Lost Customers
123	5465465	Ann	1
336	1122333	David	1
Total			2

Would be much grateful for your help

[Lil]

Dear Rico Zhou thank you so much. It is working. everything seems correct.

Thank you for help.

[v-rzhou-msft · Accepted solution]

Hi @Lil ,

I suggest you to try code as below to update your measures.

Active Customer ++ = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Customer table',
        'Customer table'[Customer ID],
        "Flag", IF ( NOT ( ISBLANK ( [MTD] ) ) && ( NOT ( ISBLANK ( [LMTD] ) ) ), 1, BLANK () )
    )
RETURN
    SUMX ( _SUMMARIZE, [Flag] )

Lost Customer-- = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Customer table',
        'Customer table'[Customer ID],
        "Flag", IF ( ISBLANK ( [MTD] ) && ( ISBLANK ( [LMTD] ) ), 1, BLANK () )
    )
RETURN
    SUMX ( _SUMMARIZE, [Flag] )

New Customer -+ = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Customer table',
        'Customer table'[Customer ID],
        "Flag", if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK())
    )
RETURN
    SUMX ( _SUMMARIZE, [Flag] )

Passive Customer +- = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Customer table',
        'Customer table'[Customer ID],
        "Flag", IF ( ISBLANK ( [MTD] ) && NOT ( ISBLANK ( [LMTD] ) ), 1, BLANK () )
    )
RETURN
    SUMX ( _SUMMARIZE, [Flag] )

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.

[Lil]

Dear Rico Zhou thank you so much. It is working. everything seems correct.

Thank you for help.