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:IDModified DateINC0000019148628/9/2017 16:27INC0000019148628/9/2017 18:04INC0000019148628/9/2017 20:22INC0000019148628/9/2017 22:39INC0000019148628/10/2017 15:21INC0000019167688/12/2017 16:07INC0000019167688/12/2017 16:25INC0000019167688/12/2017 16:27INC0000019167688/12/2017 16:32INC0000019167688/12/2017 18:37 Needs to return this:IDModified DateSequenceINC0000019148628/9/2017 16:271INC0000019148628/9/2017 18:042INC0000019148628/9/2017 20:223INC0000019148628/9/2017 22:394INC0000019148628/10/2017 15:215INC0000019167688/12/2017 16:071INC0000019167688/12/2017 16:252INC0000019167688/12/2017 16:273INC0000019167688/12/2017 16:324INC0000019167688/12/2017 18:375 Thoughts?

Anonymous

Not applicable

7 years ago

Solved

Add a sequence number by ID and date/time

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?

Need Help

Anonymous

7 years ago

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]
    )
)

Forum Discussion

Add a sequence number by ID and date/time

3 Replies

Related content

Week Number Sequence

Date/Time

Sequence Row number 1,2,3

Sequence number

Create Sequence Number using DAX