cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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:

IDSALE_DATE
109-21-2021
209-21-2021
309-21-2021
209-21-2021
309-21-2021
109-21-2021
507-22-2021
609-21-2021
909-21-2021
708-21-2021
805-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
Super User II

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

results.JPG

 

The model:

model.JPG

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

 

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

1 REPLY 1
Super User II
Super User II

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

results.JPG

 

The model:

model.JPG

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

 

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors