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.

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: