Forum Discussion

lha's avatar
lha
Icon for Helper I rankHelper I
8 years ago
Solved

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 yea...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.