How to create a measure that help to highligth customers based on continue sales

[RuthMerchán]

Hi! 

I have built a simple report. I have two tables, one of calendar dimension and another of sales with columns as client_id, date, amount_sales. I have created a matriz table where the rows are the clients_id, the columns are the months of the year and the values are the amounts_sales. there are thousand of clients and the another day casually I saw some clients that hadn't buy anything. MOstly of them buy all the months but a little of them don't buy all the months. I have created a little sample of it with a pivot table:

As you can see the client LC010 has not purchased for 4 months. It is easy to see here because it is a sample, but with thousand of data it is dificult for me to detect what clients are like this. I would like to create another table with just clients like this, for example, stablish the rule to show only clients with 4 or more months without purchasing.

Please, could you help me with a measure or some idea to create so that I can show in another table just the clients that needs attention like this example?

As filters I have only the year in my report (can be selected multiple years). 

I have tried something like this:

InactiveCustomers = 
VAR Yearselected = SELECTEDVALUE('DIM_TIME'[Year])
VAR Monthselected = VALUES('DIM_TIME'[NumberMonth])
VAR ActiveCustomers = FILTER(ALL('fact_sale'), YEAR('fact_sale'[Date]) = Yearselected )
VAR Customerwithpurchase = 
    SUMMARIZECOLUMNS(
        'fact_sales'[client_id],
        "Monthswithpurchase", COUNTROWS(ActiveCustomers )
    )
RETURN
FILTER(
    Customerwithpurchase ,
    [Monthswithpurchase] <= (COUNTROWS(Monthselected ) - 4)
)

However, It does not work and I am not sure if it has sense...

Thank you so much for your help!

[marcelsmaglhaes · Accepted solution]

Hey @RuthMerchán 
Take a look in the measure bellow, and note that while the provided measure serves as a starting point for identifying clients who may need attention based on their purchase behavior, its effectiveness depends on the specific data context and characteristics of your dataset. You may need to adapt or refine this measure to better suit your data and business requirements. Use it as a parameter to start your analysis, and be prepared to make adjustments. 
*If this post helps, please mark as solved.
Cheers, 
Marcel

Attention Needed (Multi-Year) =
VAR SelectedYears = VALUES('Calendar'[Year])
VAR SalesSummary = SUMMARIZE('Sales', 'Sales'[client_id], 'Calendar'[Year])
VAR PurchasingYears = COUNTROWS(
FILTER(SalesSummary, 'Calendar'[Year] IN SelectedYears)
)
RETURN IF(PurchasingYears < COUNTROWS(SelectedYears), "Needs Attention", "OK")

[RuthMerchán]

Hi!   

your suggestion has helped me a lot. 

Based on your answer, the formula that finally worked for me was:

 Alert_Client = VAR SelectedYear = max(DIM_TIEMPO[ANO_ID])

VAR CurrentMonth = MONTH(TODAY())-1

VAR SalesSummary = SUMMARIZE(FACT_VENTA, FACT_VENTA[CLIENTE_ID], DIM_TIEMPO[MES_NUM])

VAR PurchasingMonths = COUNTROWS( FILTER( SalesSummary, max(DIM_TIEMPO[ANO_ID]) = SelectedYear && DIM_TIEMPO[MES_NUM] <= CurrentMonth ) )

RETURN IF(PurchasingMonths < CurrentMonth, "1", "0") ​

Thank you very much for your support 🙂

BR