Get count and list of cutomers (ID) that have no sales in the last 2 months

[dollarvora]

So, I've 2 tables as under

SALES table:

ID	SALE_DATE
1	09-21-2021
2	09-21-2021
3	09-21-2021
2	09-21-2021
3	09-21-2021
1	09-21-2021
5	07-22-2021
6	09-21-2021
9	09-21-2021
7	08-21-2021
8	05-21-2021

CUSTOMER Table

ID
1
2
3
4
5
6
7
8
9
10

I want to create 2 measures:

1st would be the count of customers that have no sales in the last 2 months, so in this case it would be 2 (8 and 10)

and second measure would give the list of all those customer ID's (8 and 10)

Right now I use this measure to get the list of all ID's that have no sales in last 2 months

show_hide = VAR current_name = MIN(SALES[ID])

VAR chk_not_in = IF( COUNTROWS( FILTER( ALL(SALES), SALES[ID]= current_name && SALES[SALE_DATE])> DATE(YEAR(NOW()),MONTH(NOW())-2, DAY(NOW())) ) )= 0, 0, 1 )

VAR chk_in = IF( COUNTROWS( FILTER( ALL(CUSTOMER), CUSTOMER[ID] = current_name ) ) = 0, 0, 1 )

RETURN IF(chk_in = 1 && chk_not_in = 1, 1, 0)

So every ID with a show_hide of "0" would be the ones that dont have any sales in the last 2 months I was wondering if there's an easy way to do it and also, I'm not sure how to get the count of all those ID's

[PaulDBrown · Accepted solution]

@dollarvora 

(since you were asking referencing sales in the last 2 months (I take it for this current year) I have changed your dates in the sales table to 2020 (and not 2021 as you had in your "sales table")

Here is one way of doing this:

The model:

You can then use the following measures.

To count the customers with no sales in the last 2 months:

Count Customers no sales in Last 2 months = 
VAR ThisYear = YEAR(TODAY())
VAR ThisMOnth = MONTH(TODAY()) -2
VAR _today = DAY(TODAY())
VAR YMD = ThisYear *10000 + ThisMOnth *100 + _today
VAR _Year = YEAR(MAX(SalesTable[Sales Date])) * 10000
VAR _Month = MONTH(MAX(SalesTable[Sales Date])) * 100
VAR _DAY = DAY(MAX(SalesTable[Sales Date])) 
VAR SalesDateNUm = _Year + _Month + _DAY
VAR Customers = VALUES(CustomerTable[ID])
VAR CustomersL2Months = CALCULATETABLE(VALUES(CustomerTable[ID]), 
                        FILTER(SalesTable, 
                        SalesDateNUm > YMD))
RETURN
COUNTROWS(EXCEPT(Customers, CustomersL2Months))

To list the customers with no sales in the last 2 months:

List Customers no sales in last 2 months = 
CONCATENATEX(FILTER(CustomerTable, 
              [Count Customers no sales in Last 2 months] >0), 
                CustomerTable[ID], ", ")