Correct DAX Measure to show result even when the respective field is not selected

[Anonymous]

Hi,
I have created a DAX Measure calculation to calculate churned clients count for each Month-Year when a client has not made any sales after 30 days from their last transaction date till date (current month-year).

Lapsed Clients Count =

VAR LastTransactionDate = CALCULATE(MAX('Transaction'[Transaction Date Key]),ALL('Transaction Date'))

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

FILTER(ALL('Transaction Date'),

'Transaction Date'[Date] >= LastTransactionDate &&

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

RETURN

COUNTROWS(ClientsNoSales)

Result:

Now if I remove the Instance Name(Client Name) from the above table, everything goes blank.

Can you pls help me correct the DAX so that even if I don't select Client Name, it shows me the Lapsed Clients Count with respect to Month-Year as shown below?

[Anonymous]

Hi @Ashish_Mathur @parry2k,
Here is the link to download the report template.
https://drive.google.com/file/d/1ulYtC-30rD1CWAbc__y7lHl7IbxLAr2Z/view?usp=sharing

[Ashish_Mathur]

Hi,

I get an Access Denied message.  Also, please clarify what you mean by "made any sales after 30 days from their last transaction date till date (current month-year)".  Please illustrate with a simple example.

[Anonymous]

Hi @Ashish_Mathur,
Here is a simple example of my requirement,
I just need the count of Clients for each month-year after their last transaction date (forget about the last 30 days).
So, the below example in the screenshot is for 1 Client, where its last transaction of 655.0 was done in the month of March - 2019 and after that, there were no transactions/sales and I am trying to take the count for each month after their Last Sales date till current month (which works fine with my logic).
Now, when I remove the filter on Client, I want to display the counts of all clients who did not make any sales after their last transaction date.

The result should display something like this:

Please, let me know if you need more information and you should be able to access the report template now.
This is the logic that I am currently working with:

Test Count logic =

CALCULATE(COUNTX(VALUES('Client'[Instance Name]),[Instance Name]),

FILTER(ALL('Transaction Date'),

'Transaction Date'[Date] >= [Latest Sales Date]

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

))
and the logic for the Latest Sales date is :

Latest Sales Date = CALCULATE(LASTDATE('Transaction'[Transaction Date Key]),ALL('Transaction Date') ).

The problem with my logic is with the Latest Sales Date which is correct for each client but when I don't select Client, it gives me the Latest Sales Date as 6/29/2021 (today's date) which is not right and my count logic goes off.

Please let me know your thoughts on this?

[Ashish_Mathur]

Hi,

I do not see any data in your file

I suggest you share your tables in an MS Excel file and show the expected result there.

[Anonymous]

Hi @Ashish_Mathur,

Here is the sample output file.
https://drive.google.com/file/d/13HuRGZ9_yeslYIyXKSjEgUB7XCko8Xte/view?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[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?

[Anonymous]

Hi @Ashish_Mathur,

The below DAX gives me correct Last Transaction Date for each Client Id
Last Transaction Table = SUMMARIZE ('Transaction','Client'[Client Id],

"Last Transaction Date", LASTDATE('Transaction'[Transaction Date Key]),

"DaysSince", DATEDIFF(LASTDATE('Transaction'[Transaction Date Key]),TODAY(),DAY))

Now, can you help use this and calculate the count of churned customers after their Last Transaction Date?
I was trying the below dax calculation but it gives me the error "multiple values were supplied":

CALCULATE(COUNTX(VALUES('Client'[Instance Name]),[Instance Name]),

FILTER(SUMMARIZE('Transaction', Client[Client Id],"DaysSince", DATEDIFF(LASTDATE('Transaction'[Transaction Date Key]),TODAY(),DAY)),

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

))

Can you please help me in building the logic with this?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur,
This logic works perfectly. Thank you so much for your effort.
One last ask, can we build the Churned Clients logic only up to the end of the previous month. Since there is a possibility that the client may have a transaction in the current month and we do not already want to mark it as churned until the completion of the current month?

[Ashish_Mathur]

You are welcome.  Modify the measure to:

Churned Customers = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Clients[Client key]),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MIN('Calendar'[Date])-1)),Clients[Client key],"ABCD",[Revenue],"EFGH",[Date of last interaction]),[ABCD]=blank()&&[EFGH]<min('Calendar'[Date])&&[EFGH]<>BLANK()))

[parry2k]

@Anonymous it will be much easier if you can provide the sample pbix file. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.