How to find Customers with no Sales in selected Period Date using Slicer

[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 or CustInvoiceJour both of them are Same. 

What if I want to find customers who do 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.

How do I write DAX that does this for me ? 

• LineAmount is the column for sales amount.
• InvoiceAccount represents the customer number.
• DeliveryName is the customer name.
• InvoiceDate is the sales date.
  
  can anyone help me Please ?

[DataNinja777]

Hi @amal_01 ,

To find customers who do not have any sales within a specified date range, you can create a DAX measure that identifies customers with no transactions during the selected period and filters them in your visualization. Here’s how to achieve this:

• Identify All Customers: Extract all unique customer IDs (InvoiceAccount).
• Identify Customers with Sales in the Selected Period: Filter customers who have sales (LineAmount) in the selected date range.
• Subtract the Customers with Sales from the Full Customer List: This gives the list of customers without sales.

Create a measure to identify customers with no sales in the selected range:

NoSalesCustomers = 
VAR AllCustomers = VALUES(CustInvoiceJour[InvoiceAccount])
VAR SalesCustomers = 
    CALCULATETABLE(
        VALUES(CustInvoiceJour[InvoiceAccount]),
        CustInvoiceTrans[LineAmount] > 0,
        CustInvoiceTrans[InvoiceDate] >= MIN(CustInvoiceTrans[InvoiceDate]),
        CustInvoiceTrans[InvoiceDate] <= MAX(CustInvoiceTrans[InvoiceDate])
    )
RETURN
    EXCEPT(AllCustomers, SalesCustomers)

This measure uses the EXCEPT function to find customers in AllCustomers that are not in SalesCustomers.

2. Create a visualization using the resulting list:

Use the NoSalesCustomers measure in a table or matrix visual to display the corresponding InvoiceAccount and DeliveryName.

3. Alternatively, create a calculated table (if a separate table is preferred):

NoSalesCustomersTable = 
ADDCOLUMNS(
    EXCEPT(
        VALUES(CustInvoiceJour[InvoiceAccount]),
        CALCULATETABLE(
            VALUES(CustInvoiceJour[InvoiceAccount]),
            CustInvoiceTrans[LineAmount] > 0,
            CustInvoiceTrans[InvoiceDate] >= MIN(CustInvoiceTrans[InvoiceDate]),
            CustInvoiceTrans[InvoiceDate] <= MAX(CustInvoiceTrans[InvoiceDate])
        )
    ),
    "Customer Name", RELATED(CustInvoiceJour[DeliveryName])
)

This creates a standalone table listing all customers without sales, including their names.

Key Notes:

• Relationship: Ensure the CustInvoiceJour and CustInvoiceTrans tables are properly related using InvoiceId.
• Date Range Slicer: Connect the slicer to CustInvoiceTrans[InvoiceDate] or CustInvoiceJour[InvoiceDate].

Best regards,

[amal_01]

@DataNinja777 
thanks for your effort.
but this measure gave me the customers have sales in selected period 

[DataNinja777 · Accepted solution]

Hi @amal_01 ,

Thank you for your feedback. I’ve decided to take a different approach by visualizing customers with no sales, using a flag to mark them with a value of 1.

NoSalesFlag = 
VAR SalesInRange = 
    CALCULATE(
        COUNTROWS(CustInvoiceJour), -- Count rows in the table
        CustInvoiceJour[LineAmount] > 0, -- Include rows with sales
        CustInvoiceJour[InvoiceDate] >= MIN(CustInvoiceJour[InvoiceDate]), -- Date range start
        CustInvoiceJour[InvoiceDate] <= MAX(CustInvoiceJour[InvoiceDate]) -- Date range end
    )
RETURN
    IF(SalesInRange = 0, 1, 0) -- Return 1 for no sales, otherwise 0

Instead of using the entire customer master table to identify customers with no sales, I’ve used only the sales transaction table, CustInvoiceJour. This approach ensures that only customers relevant to the selected periods are considered for assessing no sales, as the customer master data may include old customers with no transactions in recent years.

The resulting output looks like below:

I've attached an example pbix file for your reference.

Best regards,

[amal_01]

@DataNinja777 
thanks alot.
I appreciate your time and effort