cancel
Showing results for
Did you mean:
Regular Visitor

## 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:

 555 6/12/2017 556 6/17/2017 555 7/17/2017 556 9/12/2017 555 10/12/2017 555 11/12/2017 555 12/28/2017 555 1/28/2017 556 1/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.

2 REPLIES 2
Community Support Team

## Re: Count duplicates over a certain time

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
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Count duplicates over a certain time

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 128 members 1,796 guests
Recent signins: