cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jasel Regular Visitor
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:

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Count duplicates over a certain time

@Jasel,

 

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.
Jasel Regular Visitor
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.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 128 members 1,796 guests
Please welcome our newest community members: