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.
Dear all,
Need your support!
My question is:
My data has the following rows. Customer ID(unique), visit date, and coupon.
I would like to figure out the customer who has used the coupon (BD) but has not visited in the last three months.
CustomerIDVisit Datecoupon Name
17gReRgccZaW | 2021/2/1 | BD |
pre53904 | 2021/2/1 | Lunch |
pre53904 | 2021/2/1 | Appetizer |
pre53904 | 2021/2/1 | Drink |
pre53904 | 2021/2/1 | BD |
vK07PS0WltE8 | 2021/2/1 | Lunch |
pre49066 | 2021/2/1 | Appetizer |
pre49066 | 2021/2/1 | Drink |
pre49066 | 2021/2/1 | BD |
pre49066 | 2021/2/1 | Lunch |
Xd5PbRfjDPhs | 2021/2/1 | Appetizer |
pre49066 | 2021/2/7 | Drink |
pre49066 | 2021/2/7 | BD |
pre49066 | 2021/2/11 | Lunch |
pre49066 | 2021/2/11 | Appetizer |
pre49066 | 2021/2/13 | Drink |
pre49066 | 2021/2/13 | BD |
pre49066 | 2021/2/20 | Lunch |
pre49066 | 2021/2/20 | Appetizer |
pre49066 | 2021/2/21 | Drink |
pre49066 | 2021/2/21 | BD |
pre49066 | 2021/2/27 | Lunch |
pre49066 | 2021/2/27 | Appetizer |
pre49066 | 2021/2/28 | Drink |
pre49066 | 2021/2/28 | BD |
pre08741 | 2021/2/1 | Lunch |
HkFWRRgdi2et | 2021/2/1 | Appetizer |
pre08741 | 2021/2/1 | Drink |
pre08741 | 2021/2/1 | BD |
pre08741 | 2021/2/1 | Lunch |
pre08741 | 2021/2/5 | Appetizer |
TkKP0SODOXOG | 2021/2/6 | Drink |
Is there any solution?
Solved! Go to Solution.
@jnn4282 , With help from the date table create a rolling measure
Rolling 3 = CALCULATE(countrows(filter(Table, Table[Name]="BD")), DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Use BD in Last three months and did not come
countx(values(Table[CustomerID]), if(isblank([Rolling 3]) , [CustomerID], Blank() ) )
or use BD in past did not came
Rolling 3 = CALCULATE(countrows(Table), DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Cumm= CALCULATE(countrows(filter(Table, Table[Name]="BD")),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Customer had DB , did not came in last 3
=
countx(values(Table[CustomerID]), if(isblank([Rolling 3]) && no(isblank([Cumm])) , [CustomerID], Blank() ) )
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Rolling Months Formula: https://youtu.be/GS5O4G81fww
Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Based on the data that you have shared, show the expected result very clearly.
Thank you Ashis,
What I am expecting is :
I send BD coupons to all our customers(1000 customers).
Total return (coupon used) 150.
In that 150 how many customers are frequent visiter and how many customers were there the last visit was three months ago.
suppose customer ID pre49066 is visiting frequently and used BD coupon. (Dates are dummy).
But his last visit was 3 months ago.
For my data analysis, BD coupon was a trigger to revisit.
Is this information enough for explanation.
CustomerID | Visit Date | coupon Name |
2 | 2023/1/7 | Appetizer |
3 | 2023/1/3 | Appetizer |
4 | 2023/3/16 | Appetizer |
5 | 2023/3/8 | Appetizer |
6 | 2023/2/2 | Appetizer |
7 | 2023/1/3 | Appetizer |
8 | 2023/3/20 | Appetizer |
9 | 2023/3/12 | Appetizer |
2 | 2023/3/14 | BD |
6 | 2023/3/18 | BD |
7 | 2023/3/10 | BD |
1 | 2023/3/13 | Beer |
3 | 2023/3/8 | Beer |
4 | 2023/3/9 | Beer |
5 | 2023/3/17 | Beer |
8 | 2023/3/4 | Beer |
9 | 2023/3/5 | Beer |
1 | 2022/10/1 | Drink |
2 | 2023/2/7 | Drink |
3 | 2023/1/8 | Drink |
4 | 2023/1/4 | Drink |
5 | 2022/12/10 | Drink |
6 | 2023/3/9 | Drink |
7 | 2023/2/3 | Drink |
8 | 2023/1/4 | Drink |
9 | 2023/3/21 | Drink |
2 | 2023/1/2 | Lunch |
3 | 2023/3/15 | Lunch |
4 | 2023/3/7 | Lunch |
6 | 2023/1/2 | Lunch |
7 | 2023/3/19 | Lunch |
8 | 2023/3/11 | Lunch |
9 | 2023/2/5 | Lunch |
Dear Ashis,
I have created another table to make my question more clear.
Customers count 9 ( unique ID), visited dates are from Oct to March randomly.
In March I sent Beer coupons to all customers but only 6 returned(used).
My expectation is ID 1 and 5, who used Beer coupons, ID 1's last visit was October 1 and
ID 5's last visit was December 10 before using the Beer coupon.
I want to calculate the number of customers who used the Beer coupon and their visit gap was more than 2 months.
I hope this will make it easier for you to support me.
Hi,
The last visit of ID 5 (prior to receiving the Beer coupon) in March 2023 was March 8 (not December 10). So why should ID 5 appear in the result? Also, do you just want the customer count or do you also want which where those customers?
Dear Ashis,
ID 5's last visit was on 2022/12/10 and used a Drink coupon.
I know the total used coupon="Beer". Out of those who used the coupon(here 6), how many of them have a gap of more than 3 months(here 2( ID 1 and ID 5)?
1's last visit was 2022/10/1 and 5's 2022/12/10.
My expected result is
coupon sent = 9
coupon used = 6
Frequent user =4 (who visits at least once a month)
Non-frequent user = 2 ( who has a gap of more the 3 months)
Why should ID 5 be counted as a Non-frequent user? His last visit was not more than 2 months ago. It was in March 2023 itself - for appetizer.
This was just sample data. I might have mistaken the date.
Let's assume ID 5's last visit was Dec 2022 and used an appetizer.
Non-frequent user means who hasn't visited, let's say 3 months.
That is a very poor way to post a question. The very least that i can expect from you is to post a proper/well thought over dataset abd then share the exact expected result. Don't expect people to offer you solutions and also correct mistakes in your data.
Kinldy share the final dataset and double check it and the expected result before you post.
Dear Ashish,
Thank you for your advice. I must be precise to ask you for help.
CustomerID | Visit Date | coupon Name |
1 | 2023/3/13 | Beer |
1 | 2022/10/1 | Drink |
2 | 2023/1/7 | Appetizer |
2 | 2023/3/14 | BD |
6 | 2023/2/2 | Appetizer |
7 | 2023/1/3 | Appetizer |
2 | 2023/2/7 | Drink |
2 | 2023/1/2 | Lunch |
3 | 2023/1/3 | Appetizer |
6 | 2023/3/18 | BD |
7 | 2023/3/10 | BD |
3 | 2023/3/8 | Beer |
3 | 2023/1/8 | Drink |
3 | 2023/3/15 | Lunch |
4 | 2023/3/16 | Appetizer |
4 | 2023/3/9 | Beer |
4 | 2023/1/4 | Drink |
4 | 2023/3/7 | Lunch |
5 | 2022/11/8 | Appetizer |
5 | 2023/3/17 | Beer |
5 | 2022/12/10 | Drink |
8 | 2023/3/20 | Appetizer |
6 | 2023/3/9 | Drink |
7 | 2023/2/3 | Drink |
8 | 2023/3/4 | Beer |
8 | 2023/1/4 | Drink |
8 | 2023/3/11 | Lunch |
9 | 2023/3/12 | Appetizer |
9 | 2023/3/5 | Beer |
6 | 2023/1/2 | Lunch |
7 | 2023/3/19 | Lunch |
9 | 2023/3/21 | Drink |
9 | 2023/2/5 | Lunch |
The expected result is a Customer who has used the Beer coupon and has not visited before three months.
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you so much for your great support.
Now onward I will post my expectation in detail.
You are welcome.
You still have not shown the expected result. Take dates from the current month to make your question more relevant and on that dataset, show the expected result.
@jnn4282 , With help from the date table create a rolling measure
Rolling 3 = CALCULATE(countrows(filter(Table, Table[Name]="BD")), DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Use BD in Last three months and did not come
countx(values(Table[CustomerID]), if(isblank([Rolling 3]) , [CustomerID], Blank() ) )
or use BD in past did not came
Rolling 3 = CALCULATE(countrows(Table), DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Cumm= CALCULATE(countrows(filter(Table, Table[Name]="BD")),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Customer had DB , did not came in last 3
=
countx(values(Table[CustomerID]), if(isblank([Rolling 3]) && no(isblank([Cumm])) , [CustomerID], Blank() ) )
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Rolling Months Formula: https://youtu.be/GS5O4G81fww
Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |