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 ta...
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.

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