Count repeated records by dates with respect to another record

[Syndicate_Admin]

Good

I am trying to count duplicate records Ref, if such registration occurs according to Event M, on the same day or after Event I, or how more up to 7 days after Event I.

Ref	Event	Date
Off	I	01/01/2022
Off	M	02/01/2022
Off	M	03/01/2022
Off	M	04/01/2022
CD	I	03/01/2022
CD	M	03/01/2022
If	I	04/01/2022
If	M	03/01/2022
GH	I	05/01/2022
GH	M	13/01/2022
JK	R	14/01/2022
LL	M	15/01/2022

The expected result, for table above and the premises given:

Off	3	M occurs after I, 3 times
CD	1	M occurs on the same day as I
If	0	M occurs before I
GH	0	M occurs +7days after I
JK	0	is not M and is not duplicate
LL	0	is not duplicate

Total 4 repeated Ref records.

I am looking for a measure to be able to count the total of repeated records Ref according to given premises.

I'm not sure whether to create a column or how to do it.

Thank you!

Best regards

Jojudima

[Syndicate_Admin]

Good again,

When moving the solution to the model, I find that if there is data from different months, it does not work correctly.

Ref	Event	Date
Off	I	01/01/2022
Off	M	02/01/2022
Off	M	03/01/2022
Off	M	04/01/2022
CD	I	03/01/2022
CD	M	03/01/2022
If	I	04/01/2022
If	M	03/01/2022
GH	I	05/01/2022
GH	M	13/01/2022
JK	R	14/01/2022
LL	M	15/01/2022
Off	M	01/02/2022
Off	M	02/02/2022
CD	I	03/02/2022
CD	M	04/02/2022
CD	M	10/02/2022

For this case with data in January and February the total that yields is 5, but it really should be 7.

I understand that for February it is taking the first date of I, which occurs in January, so M happens +7days later.

When grouped by months, the desired result would be 4 in January and 3 in February.

Any help will be welcome.

Thanks in advance.

Jojudima

[Syndicate_Admin]

Thank you very much, it works perfectly.

[Anonymous]

Hi @Syndicate_Admin ,

I think you can try my measure to achieve your goal.

Count M after I = 
VAR _DATE_I = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Ref]),'Table'[Event]="I"))
RETURN
CALCULATE(count('Table'[Ref]),FILTER(ALLEXCEPT('Table','Table'[Ref]),_DATE_I<>BLANK()&&'Table'[Date]>=_DATE_I&&'Table'[Event]="M"&&DATEDIFF(_DATE_I,MAX('Table'[Date]),DAY)<7))+0

Measure = 
VAR _DATE_I = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Ref]),'Table'[Event]="I"))
VAR _FIRST_M = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Ref]),'Table'[Event] = "M"))
VAR _DATEDIFF = DATEDIFF(_DATE_I,_FIRST_M,DAY)
RETURN
IF( _DATE_I = BLANK(),IF(_FIRST_M = BLANK(),"is not M and is not duplicate","is not duplicate"),IF(_DATEDIFF<0,"M occurs before I",IF(_DATEDIFF>7,"M occurs +7days after I",IF(_DATEDIFF = 0,"M occurs on the same day as I","M occurs after I, "&""&[Count M after I]&""&" times"))))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello

Thank you very much for the answer, with this method I do not get the total, it is true that it counts the duplicate records.

Best regards

Jojudima

[lbendlin]

You could do this as a column or a measure.  I think a measure is more appropriate because a column would duplicate data.

Measure Repeated = 
var a = values(Events[Ref])
var b = ADDCOLUMNS(a, "i",var r=[Ref] return CALCULATE(max(Events[Date]),all(Events),Events[Ref]=r,Events[Event]="I"))
var c = ADDCOLUMNS(b,"m",var r = [Ref] var i = [i] return CALCULATE(countrows(Events),all(Events),Events[Ref]=r,Events[Date]>=i,Events[Date]<=i+7,Events[Event]="M"))
return sumx(c,[m])

see attached