cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Super User II

(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], ", ")``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Super User II

(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], ", ")``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!