cancel
Showing results for
Did you mean:
Helper II

## sort the number of customer using a specific coupon

Dear all,

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?

2 ACCEPTED SOLUTIONS
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

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
14 REPLIES 14
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Helper II
 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.

Super User

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
Helper II

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)

Super User

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
Helper II

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.

Super User

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
Helper II

Dear Ashish,

 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

https://we.tl/t-tzLCuPOZrw

The expected result is a Customer who has used the Beer coupon and has not visited before three months.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!