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

Calculate based on date filter total q-ty of customers according to time period that has no sales

Hello all!

 

Could you kindly help!

I've a table of sales (including sales date, cutomer name and id, sales sum, etc.) and linked Dates table, and Date Filters created in PBI desktop based on 'Dates' table.

 

Table sample named "Sales": 

Sales DateCustomer NameCustomer IDProduct NameSales Manager NameQ-tyPrice per UnitSales Amount
15.01.2019Customer AA1Product XSM ABCD1100100
10.02.2019Customer AA1Product YSM ABCD2150300
20.03.2019Customer AA1Product ZSM ABCD3200600
07.04.2019Customer AA1Product XSM ABCD4100400
11.05.2019Customer AA1Product XSM ABCD5100500
23.06.2019Customer AA1Product YSM ABCD6150900
24.07.2019Customer AA1Product ZSM ABCD72001400
13.08.2019Customer AA1Product XSM ABCD8100800
17.09.2019Customer AA1Product XSM ABCD9100900
18.10.2019Customer AA1Product XSM ABCD101001000
21.11.2019Customer AA1Product YSM ABCD1150150
22.12.2019Customer AA1Product ZSM ABCD2200400
14.01.2019Customer BB2Product XSM ABCD3100300
09.02.2019Customer BB2Product YSM ABCD4150600
19.03.2019Customer BB2Product ZSM ABCD52001000
06.04.2019Customer BB2Product XSM ABCD6100600
10.05.2019Customer BB2Product XSM ABCD7100700
22.06.2019Customer BB2Product YSM ABCD81501200
23.07.2019Customer BB2Product ZSM ABCD92001800
12.08.2019Customer BB2Product XSM ABCD101001000
16.09.2019Customer BB2Product XSM ABCD1100100
17.10.2019Customer BB2Product XSM ABCD2100200
20.11.2019Customer BB2Product YSM ABCD3150450
21.12.2019Customer BB2Product ZSM ABCD4200800
15.03.2019Customer CC3Product YSM DCBA5150750
07.07.2019Customer CC3Product YSM DCBA6150900
09.09.2019Customer CC3Product ZSM DCBA72001400
02.02.2019Customer DD4Product YSM DCBA81501200
05.05.2019Customer DD4Product ZSM DCBA92001800
07.08.2019Customer DD4Product YSM DCBA101501500
08.10.2019Customer DD4Product YSM DCBA1150150
15.12.2019Customer DD4Product ZSM DCBA2200400

Customer sales activity per Month looks like this: 

Customer activity per Month.JPG

 

 

 

 

And Date Filters are classik one and allow to select Year, Quarter, Month 

Date filters.JPG

 

 

So, what i need to calculate and where really need help, as i've already look through all topics and couldn't find solution.

 

1. Calculate customers q-ty based un their unique ID/Name accoring to Period in Monthes with no sales.

for this i have measure [Period with no sales] = DATEDIFF(DATEVALUE(TODAY());DATEVALUE(MAX('Sales'[Sales date]));MONTH)*-1

For ex. [Period with no sales] for customer "Customer C" for Today date (22.12.2019) = 3 (No sales in Dec, Nov, Oct before last purchace in September)

For ex [Period with no sales] for customer "Customer D" for April (if i select this month in Date filter) = 2 (No sales in April, March before last purchace in February).

Need help: i'd like to calculate customers q-ty in different selected time period for condition IF=[Period with no sales] >= 2 (or another month q-ty) and give such selection a name fot customer purchase time like 'Customer type 1", or "Customer type 2" if [Period with no sales]>=3; Could you kindly help? 

Additional comment: if we look in March and "Customer C" - as the result for March it is his first purchace, so "Customer C" is a "New" customer. How could i work with this restriction when calculate measure? 

 

2. And another question. As we see on pic with sales through monthes for 2019, "Customer A" and "Customer B" have purchases in each of 12 monthes. What measure i need to use to calculate such customers q-ty per Year and if it is possible to make such calculation for period of 12 monthes before Selected day (ex. if i select May 2019, measure should look back for time period of May 2018 - May 2019 and calculate condition, does the each customer had purchaces in each month of selected month range).

 

Thanks a lot in advance for you help! 

3 REPLIES 3
Super User I
Super User I

Re: Calculate based on date filter total q-ty of customers according to time period that has no sale

Hi @poddubnyoi ,

 

Can you be moe specific in terms of what is the exact output  you require based on the data.

 

Are you looking at grouping of customers who bought within last X number of months from current month.

 

Can you upload sampledata( not picture ) pbix and output expected to Google/One Drive and share the link here to find a solution.

 

Cheers

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
poddubnyoi Advocate I
Advocate I

Re: Calculate based on date filter total q-ty of customers according to time period that has no sale

Hi @CheenuSing 

the link for one drive here

 

Yes, i'd like to have a nubmer of customers with NO purchase (Sales = 0) for X (selected) quantaty of monthes (for ex >=3) from Y (selected) month.

Description of desired result add to PBIX file.

 

Thanks a lot in advance!

poddubnyoi Advocate I
Advocate I

Re: Calculate based on date filter total q-ty of customers according to time period that has no sale

Hi @CheenuSing !

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

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.

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