cancel
Showing results for
Did you mean:

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

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

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

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.

## 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)
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"))

BR,
Oleg

Announcements

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

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

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

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

Top Solution Authors
Top Kudoed Authors