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

[poddubnyoi]

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 Date	Customer Name	Customer ID	Product Name	Sales Manager Name	Q-ty	Price per Unit	Sales Amount
15.01.2019	Customer A	A1	Product X	SM ABCD	1	100	100
10.02.2019	Customer A	A1	Product Y	SM ABCD	2	150	300
20.03.2019	Customer A	A1	Product Z	SM ABCD	3	200	600
07.04.2019	Customer A	A1	Product X	SM ABCD	4	100	400
11.05.2019	Customer A	A1	Product X	SM ABCD	5	100	500
23.06.2019	Customer A	A1	Product Y	SM ABCD	6	150	900
24.07.2019	Customer A	A1	Product Z	SM ABCD	7	200	1400
13.08.2019	Customer A	A1	Product X	SM ABCD	8	100	800
17.09.2019	Customer A	A1	Product X	SM ABCD	9	100	900
18.10.2019	Customer A	A1	Product X	SM ABCD	10	100	1000
21.11.2019	Customer A	A1	Product Y	SM ABCD	1	150	150
22.12.2019	Customer A	A1	Product Z	SM ABCD	2	200	400
14.01.2019	Customer B	B2	Product X	SM ABCD	3	100	300
09.02.2019	Customer B	B2	Product Y	SM ABCD	4	150	600
19.03.2019	Customer B	B2	Product Z	SM ABCD	5	200	1000
06.04.2019	Customer B	B2	Product X	SM ABCD	6	100	600
10.05.2019	Customer B	B2	Product X	SM ABCD	7	100	700
22.06.2019	Customer B	B2	Product Y	SM ABCD	8	150	1200
23.07.2019	Customer B	B2	Product Z	SM ABCD	9	200	1800
12.08.2019	Customer B	B2	Product X	SM ABCD	10	100	1000
16.09.2019	Customer B	B2	Product X	SM ABCD	1	100	100
17.10.2019	Customer B	B2	Product X	SM ABCD	2	100	200
20.11.2019	Customer B	B2	Product Y	SM ABCD	3	150	450
21.12.2019	Customer B	B2	Product Z	SM ABCD	4	200	800
15.03.2019	Customer C	C3	Product Y	SM DCBA	5	150	750
07.07.2019	Customer C	C3	Product Y	SM DCBA	6	150	900
09.09.2019	Customer C	C3	Product Z	SM DCBA	7	200	1400
02.02.2019	Customer D	D4	Product Y	SM DCBA	8	150	1200
05.05.2019	Customer D	D4	Product Z	SM DCBA	9	200	1800
07.08.2019	Customer D	D4	Product Y	SM DCBA	10	150	1500
08.10.2019	Customer D	D4	Product Y	SM DCBA	1	150	150
15.12.2019	Customer D	D4	Product Z	SM DCBA	2	200	400

Customer sales activity per Month looks like this: 

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

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! 

[CheenuSing]

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

[poddubnyoi]

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]

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)

 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