Create DAX to get count of lapsed clients

[Anonymous]

Hi All,

I have a requirement to get the clients list and the count of clients for each month who have not sold a ticket for 30 days or more from that month (so that we can flag them as lapsed clients by looking at the report).

​e.g.

February-2021 shows every client that hasn't sold a ticket since December-2020, 

March-2021 shows every client who hasn't sold a ticket since January-2021

April-2021 shows every client who hasn't sold a ticket since February-2021 and so on.

Also, if someone stopped selling in January they would appear in Feb march April may June... and if they started selling in July they would disappear.
This is how my data model is :

I have a Fact Transaction Table which has Sales Amount, Client table which has Clients Name(Legal Name), and a Transaction Date Table which has Dates and Month-Year.
Example:

Here, no transactions in 30 days would mean they have lapsed ( Although Sales Amount = 0 would still mean it's a ticket/transaction).

Can you please help me build DAX for this requirement?
I can provide more information if needed.

[Anonymous]

Hi @m3tr01d,

Thanks for the DAX, it works but I have one more issue as explained below:

In the screenshot shown, I need to show for all the month-year, the client which did not make any sales for 30 days or more from that month, Supposedly for CUARTS-TRAINING, it should show Count=1 for Nov-19, Dec-19, Jan-20, Apr-20, May-20, and so on.

This is my final DAX:

Count_Client_No_30_days =

VAR EndDate =

EOMONTH(MIN('Transaction Date'[Date Key]), -1)

VAR StartDate =

EOMONTH(MIN('Transaction Date'[Date Key]), -2)+1

VAR Sales_Last_30_Days =

CALCULATE(

SUM('Transaction'[Sales Amount]),

    ( 'Transaction Date'[Date Key] ) >= StartDate

&& ( 'Transaction Date'[Date Key] ) <= EndDate

)

Return

COUNTROWS(

    FILTER(

        VALUES( 'Client'[Legal Name] ),

        ISBLANK(Sales_Last_30_Days)

))
Thanks for your help in Advance😊

[m3tr01d]

Right now, the measure you have will use the Sales Amount in the previous Month for the Customer.

Ex:
for Sep-19, the variables in the DAX will return
StartDate = August 1st 2019
EndDate = August 31st 2019

Is it what you want to compute?

[Anonymous]

Hi @m3tr01d ,
Yes, I want to see for the previous month, if the sales were done or not, if not my Count Measure will be equal to 1 which is working fine in my logic. Now, the next step is to show Count =1 for those missing months as well. e.g

to add those months as well for which there was no transaction and calculate the same logic of Count Client 30 days as shown in the yellow highlighted rows.

[m3tr01d]

Also @Anonymous,
I need to ask why do you have a bidirectional relationship between Transaction and Client?
Do you really need it?

[m3tr01d · Accepted solution]

Hi @Anonymous ,

What you need is a measure calculating the Sales in the last 30 days. To do that, you need the Starting date of the period.
I think you can use the following measures : 

Sales_Last_30_days = 
VAR _StartDate = EOMONTH( MIN( 'Date'[Date] ), -2)+1
RETURN
CALCULATE
        SUM( 'Transation'[SalesAmount] ),
	'Transaction Date'[Date] >= _StartDate
)

_StartDate will have this value in the calculation :

Now, you want to have the List of clients where this measure is equal to 0. 
We can Iterate on each client and compute this measure, then count the client where the measure is equal to 0

Count_Client_No_30_days = 
COUNTROWS(
	FILTER(
		VALUES( 'Client'[Legal Name] ),
		[Sales_Last_30_days] = 0
	)
)

If you want to have the list of the client, you could create a table, add the client name and put this Measure as a Visual Filter then filter where Measure = 0.

I'm not sure if my explanations are clear lol.