Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
amal_01
Helper I
Helper I

How to get customers have no Sales for three previous months

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

amal_01_0-1731437185228.png

 

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.



1 ACCEPTED SOLUTION
v-denglli-msft
Community Support
Community Support

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.

vdengllimsft_0-1732008434791.png

 


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.

vdengllimsft_2-1732010556447.png

 


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.

View solution in original post

3 REPLIES 3
v-denglli-msft
Community Support
Community Support

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.

vdengllimsft_0-1732008434791.png

 


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.

vdengllimsft_2-1732010556447.png

 


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.

@v-denglli-msft 
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
Super User
Super User

@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 )



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Users online (1,370)