Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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!
Hi @CheenuSing !
Thanks for your previous support, i could solve some, but still have issues.
I create measures:
1. Nearest sales in the past =
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |