How to get customers have no Sales for three previous months

[amal_01]

I have two tables CustInvoiceJour , CustInvoiceTrans are connected by the InvoiceId field in both tables.

I added a slicer for InvoiceDate from the CustInvoiceTrans table. I want to identify customers who have no sales for three previous months from the date selected in the slicer.

• LineAmount is the column for sales amount.
• InvoiceAccount represents the customer number.
• DeliveryName is the customer name.
• InvoiceDate is the sales date.

[Anonymous · Accepted solution]

Thanks for the reply from Greg_Deckler, please allow me to provide another insight.
Hi @amal_01 ,

Please refers to the following steps.

The test data are as follows.

Use the following DAX to create a measure to identify customers in the previous three months.

ISInPre3Months = 
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))
VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
 MAXX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[InvoiceId])

Use the following DAX to create a measure that calculates sales per customer for the previous three months.

Sales for previous 3 months = 
VAR _selectedDate = CALCULATE(MAX('CustInvoiceTrans'[InvoiceDate]),
ALLSELECTED(CustInvoiceTrans[InvoiceDate]),
ALLEXCEPT(CustInvoiceTrans,CustInvoiceTrans[InvoiceDate]),
REMOVEFILTERS('CustInvoiceJour'))

VAR _tb = CALCULATETABLE('CustInvoiceTrans', DATESINPERIOD('CustInvoiceTrans'[InvoiceDate],_selectedDate,-3,MONTH))
RETURN
IF([ISInPre3Months]<>BLANK(),
SUMX(FILTER(_tb,[InvoiceId]='CustInvoiceTrans'[InvoiceId]),[LineAmount])
)

Add this two measures to the visual's filter pane and set them to “Is not blank” and "Is blank".The final result is as follows.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amal_01]

@Anonymous 
thanks alot .

What if I want to find customers who de not have any sales from period date , meaning if I selected from 1/1/2024 to 30/3/2024, want to see customers who do not have any sales in this range date, meaning they do not have any purchase invoice 

How do I write DAX that does this for me ? 

can you help me Please ?

[Greg_Deckler]

@amal_01 Create a table VAR for customers in the current month. VAR __Table1 = SELECTCOLUMNS( FILTER( ALL('Table'), [Date] >= EOMONTH( MAX('Table'[Date]), -1 ) + 1 && [Date] <= MAX('Table'[Date]) ), "Customer", [Customer] ). Create a table VAR for customers in the previous three months. VAR __Table2 = SELECTCOLUMNS( FILTER( ALL( 'Table' ), [Date] < EOMONTH( MAX('Table'[Date]), -1 ) + 1 && [Date] >= EOMONTH( MAX('Table[Date]), -4 ) + 1 ), "Customer", [Customer] ). Then do an EXCEPT. EXCEPT( __Table1, __Table2 )