Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Count duplicates over a certain time

I have a table attached to a date dimension where I want to count the number of duplicates of Column1 - figure it to be an integer.

 

A value in Column1 repeats itself without any kind of patter.  I need to figure out how many times a value repeats itself but only repeats itself after X amount of months.

 

Example:

5556/12/2017
5566/17/2017
5557/17/2017
5569/12/2017
55510/12/2017
55511/12/2017
55512/28/2017
5551/28/2017
5561/28/2018

 

So 555 duplicate count would equal 1, and 556 would be 2.

 

I'd assume there must be some kind of DAX that can be used for this formula however I haven't been able to figure it out exactly.

Need Help

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    Anonymous,

     

    You may add a measure as follows.

    Measure =
SUMX (
    Table1,
    VAR d = Table1[Date]
    RETURN
        IF (
            COUNTROWS (
                FILTER (
                    Table1,
                    Table1[Date]
                        >= DATE ( YEAR ( d ), MONTH ( d ) - 1, 1 )
                        && Table1[Date] < d
                )
            )
                = 0,
            1
        )
)
    - 1
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I'm trying this now v-chuncz-msft, but am not seing the measure once I pusblish the tabluar model and refresh Power BI.