Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jnn4282
Helper II
Helper II

sort the number of customer using a specific coupon

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

17gReRgccZaW2021/2/1BD
pre539042021/2/1Lunch
pre539042021/2/1Appetizer
pre539042021/2/1Drink
pre539042021/2/1BD
vK07PS0WltE82021/2/1Lunch
pre490662021/2/1Appetizer
pre490662021/2/1Drink
pre490662021/2/1BD
pre490662021/2/1Lunch
Xd5PbRfjDPhs2021/2/1Appetizer
pre490662021/2/7Drink
pre490662021/2/7BD
pre490662021/2/11Lunch
pre490662021/2/11Appetizer
pre490662021/2/13Drink
pre490662021/2/13BD
pre490662021/2/20Lunch
pre490662021/2/20Appetizer
pre490662021/2/21Drink
pre490662021/2/21BD
pre490662021/2/27Lunch
pre490662021/2/27Appetizer
pre490662021/2/28Drink
pre490662021/2/28BD
pre087412021/2/1Lunch
HkFWRRgdi2et2021/2/1Appetizer
pre087412021/2/1Drink
pre087412021/2/1BD
pre087412021/2/1Lunch
pre087412021/2/5Appetizer
TkKP0SODOXOG2021/2/6Drink

Is there any solution?

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

Based on the data that you have shared, show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

 

 

 

CustomerIDVisit Datecoupon Name
22023/1/7Appetizer
32023/1/3Appetizer
42023/3/16Appetizer
52023/3/8Appetizer
62023/2/2Appetizer
72023/1/3Appetizer
82023/3/20Appetizer
92023/3/12Appetizer
22023/3/14BD
62023/3/18BD
72023/3/10BD
12023/3/13Beer
32023/3/8Beer
42023/3/9Beer
52023/3/17Beer
82023/3/4Beer
92023/3/5Beer
12022/10/1Drink
22023/2/7Drink
32023/1/8Drink
42023/1/4Drink
52022/12/10Drink
62023/3/9Drink
72023/2/3Drink
82023/1/4Drink
92023/3/21Drink
22023/1/2Lunch
32023/3/15Lunch
42023/3/7Lunch
62023/1/2Lunch
72023/3/19Lunch
82023/3/11Lunch
92023/2/5Lunch

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear Ashish,

Thank you for your advice. I must be precise to ask you for help.

CustomerIDVisit Datecoupon Name
12023/3/13Beer
12022/10/1Drink
22023/1/7Appetizer
22023/3/14BD
62023/2/2Appetizer
72023/1/3Appetizer
22023/2/7Drink
22023/1/2Lunch
32023/1/3Appetizer
62023/3/18BD
72023/3/10BD
32023/3/8Beer
32023/1/8Drink
32023/3/15Lunch
42023/3/16Appetizer
42023/3/9Beer
42023/1/4Drink
42023/3/7Lunch
52022/11/8Appetizer
52023/3/17Beer
52022/12/10Drink
82023/3/20Appetizer
62023/3/9Drink
72023/2/3Drink
82023/3/4Beer
82023/1/4Drink
82023/3/11Lunch
92023/3/12Appetizer
92023/3/5Beer
62023/1/2Lunch
72023/3/19Lunch
92023/3/21Drink
92023/2/5Lunch

 

https://we.tl/t-tzLCuPOZrw

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much for your great support.
Now onward I will post my expectation in detail.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.