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

[lha]

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.

[MFelix]

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

[lha]

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

Thanks!!

[MFelix]

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

[Anonymous · Accepted solution]

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.

[lha]

This works great, thank you!