DAX logic to get the count of clients with no transaction on a monthly basis

[Anonymous]

Hi all, 

I have a dataset that looks like below (Client, Transaction, and Transaction Date table) and I am trying to get the count of clients who were inactive in a particular month after their last transaction date.
Showing the sample output for only 3 clients for sake of keeping it simple. (Client C, E, F)

Now, I need to look at the Last Transaction Date for each Client and mark all the months after that as 1, e.g. for Client C (Client Key = 17), the Last Transaction Date was 31st May 2019 with a $59 Sales Amount, so from June-2019 onwards all months should have 1 in the Logic 1 column as shown in the below screenshot. Similarly for Client F, the Last Transaction Date was on 9th Nov 2017, and therefore all months after Nov-2017 should be marked as 1.

Finally, I will need to aggregate Logic 1 on a monthly basis which shows the number of clients with no transactions. For e.g. 
if I look at Jul-2021, there are three clients (C, E, F) who are marked as 1 in the Logic 1 column. So, I am trying to get a count of 3 for Jul-2021 and a count of 2 for Jun-2021, and similarly for all months.
My dataset has a Date Range starting from 2017 till the current month.

The column Logic 1 here is only for understanding purposes, if all the above logic can be achieved in a single calculated measure that would work too. Can you please suggest DAX logic to achieve this?

Here is my logic which does not give the correct result as my DaysSinceLastTicketSold gives days for all transactions of a client rather it should be for only the last transaction date of a client.

Final Count logic =

VAR DaysSinceLastTicketSold = DATEDIFF(LASTDATE( 'Transaction'[Transaction Date Key] ),TODAY(),DAY)

VAR MonthsAfterLastTransaction= CALCULATETABLE(VALUES('Client'[Instance Name]),

FILTER(ALL('Transaction Date'),

'Transaction Date'[Date] >= MIN('Transaction Date'[Date]) - (DaysSinceLastTicketSold)

&& 'Transaction Date'[Date] < MIN('Transaction Date'[Date]) -30

))

VAR IfSalesThisMonth = CALCULATETABLE(VALUES(Client[Instance Name]),

FILTER(ALL('Transaction Date'),

'Transaction Date'[Date] > MIN('Transaction Date'[Date]) -30

&& 'Transaction Date'[Date] < MIN('Transaction Date'[Date])))

RETURN

COUNTROWS(EXCEPT(MonthsAfterLastTransaction,IfSalesThisMonth))+0

[Anonymous · Accepted solution]

Hi @v-kelly-msft,
I have got the solution to this requirement.

Last Transaction Date =

CALCULATE(MAX('Transaction'[Transaction Date Key]),DATESBETWEEN('Transaction Date'[Date Key],MINX(ALL('Transaction Date'),'Transaction Date'[Date Key]),MAXX(ALL('Transaction Date'),LASTDATE('Transaction Date'[Date Key]))))
Lapsed Clients = 

COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Client[Client Key]),

DATESBETWEEN('Transaction Date'[Date Key],MINX(ALL('Transaction Date'),'Transaction Date'[Date Key]),MAX('Transaction Date'[Date Key]))),

Client[Client Key],"ABCD",[Total Client Revenue],"EFGH",[Last Transaction Date]),[ABCD]=blank()&&[EFGH]<min('Transaction Date'[Date Key])&&[EFGH]<>BLANK()))

[Ashish_Mathur]

Hi,

You had posted the same question earlier as well.  I remember replying to that post with my solution PBI file.

[Anonymous]

Hi @Ashish_Mathur 
Thank you for your reply, I have looked into the logic but I believe it is not giving correct results.
here is the sample output:
1) say for Client Id = 35323, Date of last interaction = 12/3/2020 and so the Churned Customers logic should show 1 for dates after Dec-2020, but it shows for all the dates (from Jan-2017 till Dec-21)

Here is another example,
2) for Client Id = 541, Date of last interaction = 10/7/2019 and so the Churned Customers logic should mark 1 from November 2019.

and a similar thing happening with the Client Id = 30025, Date of Last interaction = 10/3/2020, but churned customers logic shows 1 from Jan-2020 rather than Nov-2020.
If you could please help me resolve these errors or provide an alternative approach?

[v-kelly-msft]

Hi @Anonymous ,

Could you pls provide your .pbix file for test?Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!