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
CIWEB
Helper I
Helper I

Tracking Customers Year Over Year

Hello I was wondering if anyone had any suggestions as to how I could track a customer ID#, date, and product type, to put together a list of which customer's are returning year after year, and how many items they are purchasing? I have the data captured but do not know how to display this visually... Thanks for the help!

2 ACCEPTED SOLUTIONS

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

Hi,

 

You have to go to Modelling > New Table and then write that formula in the formula bar.


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

View solution in original post

22 REPLIES 22
v-ljerr-msft
Employee
Employee

Hi @CIWEB,

 

Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue? Do mask sensitive data before posting. Smiley Happy

 

Regards

MAIL #Purchase DateProduct Type
35410/7/2016Day Pass
21410/8/2017Night Pass
21511/1/2017Day Pass
78911/1/2017Day Pass
48511/1/2017Night Pass
7842/6/2015Day Pass
7842/6/2015Day Pass
7842/6/2015Day Pass
35410/7/2017Day Pass
21510/9/2015Day Pass
78910/1/2014Night Pass
35410/7/2018Day Pass
21410/8/2015Night Pass



Thank you for the responses. Above is the data that I would like to have analyzed...I am hoping to get results that look something like this...

YearTotal Customers by Mail #Number of Products PurchasedNew CustomersReturning Customers
20151533150
20162030164
201730551515
20181003507525





I would also be interested in Identifying specific customers by Mail# that held a pass in one given year, did not return the following year, but still returned at a later date. 


Hopefully that makes sense. Please let me know if you have any futher questions!

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


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

Thank you I think that should work, however I am getting an error with computing the date. I typed 

Date = CALENDAR(MIN(Data[Purchase Date]),MAX(Data[Purchase Date]))

and I am getting an error of

"A table of multiple values was supplied where a single value was expected."

Any idea what is causing this error?

Hi,

 

You have to go to Modelling > New Table and then write that formula in the formula bar.


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

Would it also be possible to calculate the average number of tickets sold to the customers? As a measure it looks like I can not just have it average in a visualization like I was hoping. Would it also be possible to determine something like "55 customers purchased between 0-2 tickets in 2015", "65 Customers purchased between 2-4 Tickets in 2015"?


Thanks so much for the help.

Hi,

 

I think this would entail writing individual formulas for each interval that you want to study such as 0-2, 2-4 etc.


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

I am now revisiting this topic, if I wanted to calculate Returning Customers as individuals who have made at least two purchases over five years, and add a new category as Infrequent Customers as individuals who made only one purchase in the past five years, and New Customer as someone who purchased this year but none in the previous five years. Could that be done using a similar formula?

Hi,

 

Share some data (for 5 years atleast) and show the expected result there.


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

BI Sample.JPG

BI Sample.JPG


















So I would like to have it so that if the Mail # appears for five consecutive years for them to be considered Returning. If the Mail # appears between 4-5 times in five years to be Considered Infrequent. When the Mail # occurs for the first time for it to show up as New.

Is this possible?

Thanks!

Hi,

 

I do not understand.  For Mail # 99, what is the logic of 0,1,1,1,0?  Explain in detail.


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

The first year a Mail # appears they are considered New, and recieve a 1.

The Second year a Mail # appears within a five year period they are no longer considered New and are now considered Infrequent, so they have a 0 for the New column, and a 1 for the Infrequent column.

The Third year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.

The Fourth Year a Mail # appears within a five year period they remain as Infrequent and get a 1 in the Infrequent column.

The Fifth Year a Mail # appears within a five year period they are no longer considered Infrequent and are now considered Returning, so they have a 0 for the Infrequent column, a 0 for the New column and a 1 for the Returning column.

If a Mail # appears each year for more than five consecutive years they recieve a 1 in the Returning column for each of the years after year five.

Does this make more sense?

Hi,

 

Download my PBI file from here.  Please check this thoroughly and revert.

 

Hope this helps.

 

Untitled.png


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

Great thank you so much, the only thing is am I not able to get a count of returning, new, or infrequent by year?

If I change the Mail # to a count it looks like it turns the whole display chart blank. I would love to be able to take the data and get a count of each of those customer types by year...


Thanks

Hi,

 

I have done it for new customer only.  Please apply the same concept for the others yourself.  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/

I am trying to do the same thing. How would you account for any duplicate data points that appear?

Hi,

I do not understand your question.  Share some data and show the expected result.


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

In the example you provided you only did New Customers, I just attempted to apply those steps to all customers but my totals do not come out? I had assumed it had to do with duplicated data but maybe I misunderstood your example. Are you able to provide a file with the steps applied to Returning and Infrequent as well?

Hi,

Share some data and show the expected result.


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

I want to use the same logic the previous poster used. We use "Reservation Number" in place of Mail #. I tried to follow the sample that you provided but you only provided the steps for "New". Can you explain how you apply the logic to Infrequent and Returning?

BI Sample.JPG

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.