Create a column to sort dates

[jobf]

Please see the table below:

Field	Date
P1	01/01/2025
P1	02/25/2025
P1	01/22/2025
P2	01/10/2025
P2	03/02/2025
P2	02/15/2025
P2	01/30/2025

This table shows the dates that a certain product was applied to a certain field. I need a column that shows the application number that occurred on that date. It should look like this:

Field	Date	Application
P1	01/01/2025	1
P1	02/25/2025	3
P1	01/22/2025	2
P2	01/10/2025	1
P2	03/02/2025	4
P2	02/15/2025	3
P2	01/30/2025	2

[Anonymous · Accepted solution]

Thanks for ryan_mayu, wini_R and parry2k's concern about this issue.

Hi, @jobf 

You can try the wini_R and ryan_mayu methods and also I will share another method which I hope will help you:

You can create a calculated column based on the following DAX formula:

Application = 
VAR CurrentField = 'Table'[Field]
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Field] = CurrentField &&
        'Table'[Date] <= CurrentDate
    )
)

I have attached the pbix file for this example below, hope it helps you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@jobf 

you can try to create a column in PQ

= Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Field]=[Field] and x[Date]<=[Date])))

or you can try to use DAX to create a column

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Date]<=EARLIER('Table'[Date])))

pls see the attachment below

[wini_R]

Hey @jobf,

If by chance you would like to add a column in Power Query,  then you can try the method below. Please paste the code in Advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFSUDAw1AciIwMjU6VYHYSokb6RKaYoUKERkqgRTNTQAFPUWN8Ai1ojfUNTbCYYw0yIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Field"}, {{"all", each Table.AddIndexColumn(Table.Sort(_, {{"Date", Order.Ascending}}), "Application", 1, 1 )}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Application"}, {"Date", "Application"})
in
    #"Expanded all"

Output table:

[parry2k]

@jobf you can use RANK or ROWNUMBER function to calculate it. There are many blogs/videos on it.