cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Find last transaction date for each customer before date

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
Highlighted
Super User IV
Super User IV

Re: Find last transaction date for each customer before date

@BIExcel - 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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: Find last transaction date for each customer before date

Hi,

Share a simple dataset and show the expected result.


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

Re: Find last transaction date for each customer before date

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

Highlighted
Super User III
Super User III

Re: Find last transaction date for each customer before date

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/
Highlighted
Frequent Visitor

Re: Find last transaction date for each customer before date

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

Highlighted
Super User III
Super User III

Re: Find last transaction date for each customer before date

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

Highlighted
Frequent Visitor

Re: Find last transaction date for each customer before date

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.

 

Highlighted
Super User III
Super User III

Re: Find last transaction date for each customer before date

You are welcome.


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors