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

How do I find all the values that repeat every year?

Hello,

 

I have a column that reports the Customer Name. I have another column that reports the date the customer was first retained.

 

How would I return the Customer Name that repeats every year in a desired time frame? I want to know which customers are repeat customers year after year.

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

An alternate suggestion.  Are you simply wanting to count the number of period a customer does business with you?  For example, if a customer does business with you through 2016 and 2017, you want to get a count of 2?  The below method will let you specify that period, using a column from a date table.

 

Start by creating a simple distinct count measure

 

Customer Count = DISTINCTCOUNT(Table[Customer Name])

Next we iterate that count across whatever time period you want to do:

Customer Repeats = SUMX(
	VALUES(DateTable[Year]),
	[Customer Count]
)

Now put [Customer Repeats] into a table with 1 Customer Name as your row labels.

View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @lha,

You could probably make a measurr.that looks.something like this

Repetead = CALCULATE ( Count(Table[Customer Name]) ; ALSELECTED (Table[Year]))

Don't know how you have your model.setup but should be something around this type of measure.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Felix, would it be a count function if I want to know the actual customer not just the number of customers?


Thanks!!

For this measure to work you need to setup a table with.customer name.and the. Measure.so you would get the.count of.each one along.side.with the name.

If you want a list with the name you can use the.measure as a filter.to your table.and select.those.above 1.

Can you share sample data.and expected.result?

Regards

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

An alternate suggestion.  Are you simply wanting to count the number of period a customer does business with you?  For example, if a customer does business with you through 2016 and 2017, you want to get a count of 2?  The below method will let you specify that period, using a column from a date table.

 

Start by creating a simple distinct count measure

 

Customer Count = DISTINCTCOUNT(Table[Customer Name])

Next we iterate that count across whatever time period you want to do:

Customer Repeats = SUMX(
	VALUES(DateTable[Year]),
	[Customer Count]
)

Now put [Customer Repeats] into a table with 1 Customer Name as your row labels.

This works great, thank you!

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.