cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
poddubnyoi Advocate I
Advocate I

Customer calculation based on time they've no purchases and filtered by month through year

Hello!

I have two tables: "Sales Table" (inc. Customer name and ID, sales date, product, sales person, etc.) and "Dates Table (based on it in PBI dashboard use date filters to choose Year, Quarter, Month and desired period (Month, Week, Day)).

a) Sample of "Sales Table" is below with the link to Excel: 

Excel Sample  

Sales Table.JPG

b) Based on this table i have sales dynamics by Customers through Year 2019:

Customer activity per Month.JPG

 

 

 

 

 

 

c) Date selection filters are common:

Date filters.JPG

 

 

 

d)I have some "measures" for "Sales Table":

[Duration] = DATEDIFF(DATEVALUE(TODAY());DATEVALUE(MAX('Sales Table'[Sales Date]));MONTH)*-1 -- this measure shows how much time (in Monthes) has come from selected in date filter (it could be current date, or selected specific) to last purchase in the past period. For ex: for "Customer C" and Today (22.12.2019) it is equal 3 Monthes; for "Customer D" and July, it is equal 2 Monthes.

[First Purchase Date] =IF(HASONEVALUE('Sales Table'[Customer Name]);MINX('Sales Table';'Sales Table'[Sales Date])) -- this measure shows the Date of First Purchase from Customer. For ex: for "Customer C" - it is equal for March and 15.03.2019

So, where i need help, because i couldn't find solution through topics in Community:

1. I need to calculate Customers quantity based on "[Duration]". It is mean, that for ex:

If [Duration] = 3 Monthes, and Selected date is June, measure will respond me with #1 (Customer C, who has no Purchases in Jun, May and April); 

If [Duration] = 2 Monthes, and Selected date is April, measure will respond #1 (Customer D, who has no Purchase in April and May).

I need to get only number, to provide such customers with different Status (like cold, warm, etc.) 

And other moment, is how to avoid calculations of New customes? For ex, Customer C has first purchase in March, how it is better to add [First Purchase Date] to this solution? 

2. Customer A and Customer B have purchases in each month through 12 monthes of 2019. But, how it is should work, to calculate measure which will show such customers number but starting from chosen Month by date filter? For ex, if i select May, 2019 - measure need to look through period of May, 2018 - May, 2019.

 

Thanks a lot in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

12 REPLIES 12
Community Support
Community Support

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi poddubnyoi,

You could refer to my sample for details. You also could refer to Display Last N Months & Selected Month using Single Date Dimension in Power BI  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV
Super User IV

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi,

Share the link from where i can download your PBI file.  Please ensure that content in that file is in English.


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

Re: Customer calculation based on time they've no purchases and filtered by month through year

Dear @Ashish_Mathur  hello!

Thanks a lot for possible support!

Link to PBIX here: LINK 

 

To be clear, what i need to solve: 

i have a sales table like bellow on screen: 

pic 1.JPG

For selected JULY month i need to calculate in separate measure quantaty of customers, who DID NOT purchase for 3 monthes from JULY to past, including July (i mean time period for Jul - Jun-May no sales customers) - for Jul such number should be 0; 

If i select Jun - measure should be 1 (Customer C), the same for December
i've try different scenarios like the Number of monthes between transactions (sales dates) but couldn't connect this measure to selected Month or Date (for ex. 11.07.2019)... 

 

Thanks a lot in advance!

Super User IV
Super User IV

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi,

Download the PBI file from here.

Hope this helps.

Untitled.png


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

Re: Customer calculation based on time they've no purchases and filtered by month through year

@Ashish_Mathur 

Thanks a lot! It's work for.

But still i have a question which couldn't solve - how to calculate month difference beetween sales according to selected Month or Date, for purpose to use it like a trigger? 

For ex. If i select June,  i'd like to count customers number with Month Difference between purchaces >=3  (or another number). Or it it is better to change number in {EDATE(MIN(DATES[Date]);-2)} in your measure? 

I asked because as i understand if "customer X" fisrt purchase in Jan, 2019, last purchase in Sep, 2019 - your measure would calculate him at any case if i use it. But in case i need to asign "Status 1" to customer whith no purchases for 3 monthes, and "Status 2" for customer with no purchases for >=4 monthes, how it's operate?

Thanks a lot in advance!

Super User IV
Super User IV

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi,

You are welcome.  If my previous reply helped, please mark that one as Answer.  If you want to segment your customers depending upon the number of months for which they have not registered a purchase, then share those buckets that you want to create.


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

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi @Ashish_Mathur !

Thanks for your previous support, i could solve some, but still have issues.

I create measures:

1. Nearest sales in the past =

-- measure is looking back in the past and shows the latest month in which customer has purchase
CALCULATE(MAXX(sales;sales[Sales Date]);FILTER(ALL(DATES);DATES[Date]<=MAX(DATES[Date])))
 
2. Last date in selected Month =
-- this measure shows last date (end date) of selected month
MAX(DATES[Date])
 
3. Customer Status =
--measure granted different statuses to customers based on their sales behavior
IF([Month Difference] = 1;
"WARM";
IF([Month Difference]=2;
"PRE-LOST";
IF([Month Difference]=3;
"LOST";
IF([Month Difference]>=4;
"COLD";
IF([Month Difference]=0;
"CURRENT";
IF([Month Difference]=BLANK();
"NEW in Future"
-- also i have a question: how to calctulate, if customer purchases each month (like Customer Number 2?) and give him status "LOYAL"?
))))))
 
4. at the end i have correct table like on picture for selected month (March, 2019)
March,19.JPG
 But i couldn't correctly calculate total q-ty for each Customer Status to use it in separate visual. For WARM - it should be 1, but i get 3... where is mistake? I use follow to calculate: 
WARM Q-ty = CALCULATE(DISTINCTCOUNT(sales[Customer ID]);FILTER(ALL('Measures table');[Customer Status]="WARM"))
 
Link to PBIX: One Drive 
 
Tnx in advance!
 
BR,
Oleg
Highlighted
Super User IV
Super User IV

Re: Customer calculation based on time they've no purchases and filtered by month through year

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

poddubnyoi Advocate I
Advocate I

Re: Customer calculation based on time they've no purchases and filtered by month through year

@Ashish_Mathur  Thanks a lot! it works!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors