Forum Discussion

jobf's avatar
jobf
Helper II
1 year ago
Solved

Create a column to sort dates

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
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

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

  • wini_R's avatar
    wini_R
    Solution Supplier
    1 year ago

    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:

     

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    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