Add a sequence number by ID and date/time

[Anonymous]

I have a table with an alphanumeric ID field and a modified date & time field. I need another column that assigns a sequence numer by ID and date/time. 

So this:

ID	Modified Date
INC000001914862	8/9/2017 16:27
INC000001914862	8/9/2017 18:04
INC000001914862	8/9/2017 20:22
INC000001914862	8/9/2017 22:39
INC000001914862	8/10/2017 15:21
INC000001916768	8/12/2017 16:07
INC000001916768	8/12/2017 16:25
INC000001916768	8/12/2017 16:27
INC000001916768	8/12/2017 16:32
INC000001916768	8/12/2017 18:37

Needs to return this:

ID	Modified Date	Sequence
INC000001914862	8/9/2017 16:27	1
INC000001914862	8/9/2017 18:04	2
INC000001914862	8/9/2017 20:22	3
INC000001914862	8/9/2017 22:39	4
INC000001914862	8/10/2017 15:21	5
INC000001916768	8/12/2017 16:07	1
INC000001916768	8/12/2017 16:25	2
INC000001916768	8/12/2017 16:27	3
INC000001916768	8/12/2017 16:32	4
INC000001916768	8/12/2017 18:37	5

Thoughts?

[Anonymous · Accepted solution]

Can add this as a calculated column:

Sequence = 
VAR CurrentID= Table1[ID]
VAR CurrentDate = Table1[Modified Date]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER ( 
        ALL( Table1 ),
        CurrentID = Table1[ID]
        && CurrentDate >= Table1[Modified Date]
    )
)

[Anonymous]

@Anonymous  is there a way to allow duplicate numbers in the sequence? I have a table with multiple rows for the same ID and date and want these to have the same sequence value.

Thanks

[Anonymous]

Works perfect, thanks!