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

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
Ashish_Mathur
Super User
Super User

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/

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!

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/

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

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/

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

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/

@Ashish_Mathur  Thanks a lot! it works!

You are welcome.


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

Dear @Ashish_Mathur , hello once again!

 

What you'll recomend to do, in case i need to use "dynamic" indicator in the backet? I mean that i have a number of monthes, when customer purchased, and it goes from 0 to Max number of selected month in period.

I could calculate it like a measure, but couldn't add like a row to calculated column.

Advise please right direction? 

 

PS: if you need file in the same one drive location (i speak about measure "Month q-ty with purchases" and try to calculate "consistent" customers)

 

Thanks a lot in advance and wish you all the best in NY2020!

 

BR,

Oleg

Hi,

I do not understand your question at all.


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

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.

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.