Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
b) Based on this table i have sales dynamics by Customers through Year 2019:
c) Date selection filters are common:
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share the link from where i can download your PBI file. Please ensure that content in that file is in English.
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:
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.
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.
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 =
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
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.
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |