topic Re: How to get customers have no Sales for three previous months in DAX Commands and Tips

How to get customers have no Sales for three previous months

amal_01 — Tue, 12 Nov 2024 19:00:10 GMT

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.

Re: How to get customers have no Sales for three previous months

Greg_Deckler — Tue, 12 Nov 2024 19:39:08 GMT

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 )

Re: How to get customers have no Sales for three previous months

Anonymous — Tue, 19 Nov 2024 10:07:00 GMT

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.

Re: How to get customers have no Sales for three previous months

amal_01 — Thu, 21 Nov 2024 08:10:44 GMT

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 ?