Forum Discussion

JayV's avatar
JayV
Icon for Helper I rankHelper I
3 years ago

Find duplicates between "date selected" and "date selected" - 30 days

Hi! ðŸ™‚
I have date filters (slicer) for year, month and day in a model that uses a table with [ID] and [Date]
I want to show the duplicates between the "Date selected" and ("Date selected" -30 days)
This is the table:

If i select "December 1st, 2022" it should only show these:

Thank you!

Date
date range
dates
datesbetween
datesinperiod
duplicate
Duplicates
EARLIER
Filter
Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    JayV , Try meausres like this

     

    Have duplicate
    measure =
    var _cnt = calculate(count('Table'[ID]))
    return
    countx(Values('Table'[ID]), if(_cnt>1, [ID], Blank()))


    Have No duplicate
    measure =
    var _cnt = calculate(count('Table'[ID]))
    return
    countx(Values('Table'[ID]), if(_cnt>1, [ID], Blank()))

    • JayV's avatar
      JayV
      Icon for Helper I rankHelper I
      3 years ago

      Excuse me, where should i use this or how could i add the -30 day filter? should i add it to my existing dax?
      Thanks for the help!

  • JayV's avatar
    JayV
    Icon for Helper I rankHelper I
    3 years ago

    I tried using EARLIER() in a calculated column:

     

    Repetitive_column =
IF (
    COUNTROWS ( FILTER ( 'Table', 'Table'[ID]  = EARLIER ( 'Table'[ID] ) ) )
        > 1,
    "YES",
    "NO"
)

     

    I also tried using this measure and it almost worked (adding the Repetitive_column to the filters if "YES"); It also shows non-duplicates inside the 30d range (if it is a duplicate it shows the row that's inside the 30d range and exclude the one outside):

     

    Datefilter_measure =
CALCULATE (
    COUNTROWS('Table'),
    DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -30, DAY ) 
)