Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Find last transaction date for each customer before date

Hello DAX experts,

I cannot manage with one case. I need to calculate number of lost customers every month. The hardest thing for me is that it must be KPI dynamic report, so i need to show the changes between months. How many customers we lost every month per year. I quess that i need to use a measure. 

 

Here is my sample dataset:

 

#TypeDoc No.Post DateBP CodeNet AmountCountry
1A/R Invoice200100000107/01/2020C00483910.89PL
2A/R Invoice200100000207/01/2020C005842754PL
3A/R Invoice200100000309/01/2020C00021499.23PL
4A/R Invoice200100000409/01/2020C000451044PL
5A/R Invoice200100000509/01/2020C000442144.5PL
6A/R Invoice200100000609/01/2020C0063636.57PL
7A/R Invoice200100000709/01/2020C0052478.02PL
8A/R Invoice200100000809/01/2020C0063734.13PL
9A/R Invoice200100000909/01/2020C00638126.72PL
10A/R Invoice200100000111/03/2020C00483910.89PL
11A/R Invoice200100000211/03/2020C005842754PL
12A/R Invoice200100000311/03/2020C00021499.23PL
13A/R Invoice200100000411/03/2020C000451044PL
14A/R Invoice200100000511/03/2020C000442144.5PL
15A/R Invoice200100000611/03/2020C0063636.57PL
16A/R Invoice200100000711/03/2020C0052478.02PL
17A/R Invoice200100000811/03/2020C0063734.13PL
18A/R Invoice200100000911/03/2020C00638126.72PL
19A/R Invoice200100001911/03/2020C000421077.43PL
20A/R Invoice200100002011/03/2020C001531232.91PL

 

The output table should looks like below:

Year123456789101112
2020            
Lost Customer1254657644       
New Customer            
Loyal Customer            
Returned Customer            
 123456789101112
2019            
Lost Customer125465764476125465764476
New Customer            
Loyal Customer            
Returned Customer            

 

 

Now i have created the calculated column like below, but it shows only the proper result up to date:

 
Lost customer = if( DATEDIFF( CALCULATE (
LASTDATE ( tb_invoices[Data księgowania] ),
ALLEXCEPT ( tb_invoices, tb_invoices[BP Code] )),TODAY(),DAY)>30,1,0)

 

Before i will prepare the correct one measure i have tried to display the last transaction date for each BP code like below. Unfortuately it shows only the last tranasaction date within particular month (not in all data range before each EndOfMonth date).
Untitled.png

 

remarks:

"Data ksiegowania" means Post date.

The lost customer doesnt buy anything for at least 30 days.

 

Thank you in advance.

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.  Based on the data that you have shared, the result should be 9 for Feb 2020.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@Ashish_Mathur @Greg_Deckler  I have edited my post as requested.

Hi,

Why are there 2 rows for Lost Customers for 2020?  Also, does 12 mean that you lost 12 customers in January 2020 (as compated to December 2019)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur  sorry i mean 2019. Yes it does. I will edit it.

Hi,

You may download my PBI file from here.  Based on the data that you have shared, the result should be 9 for Feb 2020.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you @Ashish_Mathur for your help and time. That is the best solution which i get as far.

 

Measure= COUNTROWS(FILTER(SUMMARIZE(ALL(Data[BP Code]),Data[BP Code],"ABCD",[Amount],"EFGH",[Amount in previous month]),[ABCD]=0&&[Amount in previous month]>0))

 

My thoughts regarding this solution:

We don'nt need to calculate how many days we have without any purchase for each customer. What we need to say that they don't buy at least 30 days is to compare two neighbours months. We need to remember that it considers only two months data range. It does'nt count lost customers from couple months ago up to date. For the second in the begiging of the current month we will have the highest number of lost customers because of zero total amount in the current month.

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Share a simple dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@Anonymous - I have a lost customer's measure in my DAX Cookbook. If you could provide some basic information could probably adapt that to your situation. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.