Sequencing column by group in other column

[mryoan04]

Hi, I'm new to PBI. I have below columns Invoice Number, Transaction Date, and Invoice Sequence. Based on the Invoice Number, there are 6 invoices, sequenced by the number and the transaction date. However, I want to return another column (Sequenced by Date Only) that invoices with same transaction date will be grouped and return the same sequence number. Please advise how to get this column?

Invoice Number	Transaction Date	Invoice Sequence	Sequence by Date Only
A3457	7/31/2024	1	1
A3458	7/31/2024	2	1
A3459	8/1/2024	3	2
A3460	8/1/2024	4	2
A3461	8/2/2024	5	3
A3462	8/3/2024	6	4

Thank you!

[DataInsights · Accepted solution]

@mryoan04,

Try this calculated column:

Sequence by Date Only = 
RANK (
    DENSE,
    ALL ( 'Table'[Transaction Date] ),
    ORDERBY ( 'Table'[Transaction Date], ASC )
)

[mryoan04]

Awesome! It works! Thanks so much!

[mryoan04]

Hi, @DataInsights thanks for the answer!

It works with some extent. I think I got the idea now, but I forgot to add one more parameter to the table to become the filter, which is column "Type". See below table. How to reset every sequence with the filter of column type?

Type	Invoice Number	Transaction Date	Invoice Sequence	Sequence by Date
A	A3457	7/31/2024	1	1
A	A3458	7/31/2024	2	1
A	A3459	8/1/2024	3	2
A	A3460	8/1/2024	4	2
A	A3461	8/2/2024	5	3
A	A3462	8/3/2024	6	4
B	A3551	7/31/2024	1	1
B	A3552	7/31/2024	2	1
B	A3553	8/1/2024	3	2

[DataInsights · Accepted solution]

@mryoan04,

Note the PARTITIONBY argument:

Sequence by Date and Type = 
RANK (
    DENSE,
    ALL ( 'Table2'[Type], 'Table2'[Transaction Date] ),
    ORDERBY ( 'Table2'[Transaction Date], ASC ),
    PARTITIONBY ( 'Table2'[Type] )
)

[DataInsights · Accepted solution]

@mryoan04,

Try this calculated column:

Sequence by Date Only = 
RANK (
    DENSE,
    ALL ( 'Table'[Transaction Date] ),
    ORDERBY ( 'Table'[Transaction Date], ASC )
)