Flag only later repeat values that occurred within a specific time period prior

[Anonymous]

I am trying to flag duplicate values but only from the first duplicate value that type. 

So Far I can get all of the duplicates to be flagged using:

Column = 
Var Equipment = [Fault Equipment]
Return Calculate(If(COUNTROWS(Sheet1)>1,"Yes","No"),All(Sheet1),Sheet1[Fault Equipment]=Equipment)

My challenge comes in where I try to first integrate the date into this, i have tried using as the second filter argument of 

Sheet1[FaultDate]=Calendar(Earlier(Sheet1[FaultDate].[Date]),Earlier(Sheet1[FaultDate].[Date]))

I also tried using the DatesBetween function, but just can not seem to get it to even compile. Having tried using the DateAdd function as well as manually decomposing and re-composing the date values

DatesBetween((Sheet1[FaultDate]),DateAdd(Earlier(Sheet1[FaultDate]),-30,DAY),Earlier(Sheet1[FaultDate])))


DatesBetween((Sheet1[FaultDate]),Date(Year(Earlier(Sheet1[FaultDate])),Month(Earlier(Sheet1[FaultDate]))-1,Day(Earlier(Sheet1[FaultDate]))),Earlier(Sheet1[FaultDate])))

FaultRef	FaultDate	Fault Equipment	Repeat	Repeat Date
1	2021/02/06	A	No	-
2	2021/02/17	B	No	-
3	2021/02/26	C	No	-
4	2021/02/27	A	Yes	2021/02/06
5	2021/03/03	D	No	-
6	2021/03/08	P	No	-
7	2021/03/12	D	Yes	2021/03/03
8	2021/03/24	C	Yes	2021/02/26
9	2021/04/06	A	No	-
10	2021/04/10	E	No	-

I attached an example table to show what I would want the output to be in the last two columns

 Take note that Line 1 and 3 should not be flagged as that would be the first entries. Also Line 9 is not flagged as it is more that 30 days since the previous entry.

Any hints/guidance on where I am going wrong would be highly appreciated.

[v-jingzhang]

Hi @Anonymous 

Have you tried @HotChilli 's formula? It should work for Repeat Date column. For Repeat Flag column, I modify it into below one

Flag = 
Var Equipment = Sheet1[Fault Equipment]
VAR _fDate = Sheet1[FaultDate]
VAR _dateOfDup = CALCULATE(MAX(Sheet1[FaultDate]), FILTER(Sheet1, Sheet1[Fault Equipment] = Equipment && Sheet1[FaultDate] < _fDate))
VAR _dateDiff = DATEDIFF(_dateOfDup, _fDate, DAY)
RETURN 
IF(_dateDiff < 30 && NOT(ISBLANK(_dateDiff)), "Yes", "No")

Best Regards,
Community Support Team _ Jing

[HotChilli · Accepted solution]

Can you try this column please 

ColumnX = 
Var Equipment = Sheet1[Fault Equipment]
VAR _fDate = Sheet1[FaultDate]
VAR _dateOfDup = CALCULATE(MAX(Sheet1[FaultDate]), FILTER(Sheet1, Sheet1[Fault Equipment] = Equipment && Sheet1[FaultDate] < _fDate))

RETURN 
IF(DATEDIFF(_dateOfDup, _fDate, DAY) < 30, _dateOfDup)

[Anonymous]

Hi Thanks, 

This works perfectly!!! 

Just for my own information, in the Calculate function, in the filter, which one of the arguments is referring to the "current" row for the calculation? Would it be the Sheet1[Fault Equipment] or the variable that was declared?

Then as an added thought, would it be possible to do this in a Measure instead of a calculated column?