Forum Discussion

mespiritu's avatar
mespiritu
Helper I
2 years ago

Help sort 12 rolling months in order

Can someone help assist with how to sort the following by earliest month year Jul23 to latest June24 - 

 

I'm trying to sort by Attritubue using Jul23 as the first month to June24 as the last. I added a column "12 rolling months" to see if that could help with the sorting but I can't get it to work. 

 

Need Help

8 Replies

  • collinsg's avatar
    collinsg
    Solution Sage
    2 years ago

    Good day mespiritu ,

    As it stands "Rolling months" does not give a unique sort order for "Attribute" as it provides no distinction between the same month name in different years. To provide such a distinction you could

    1. Add a custom column (which I call "Sort Date") which converts "Attribute" to the first of the month.
    2. In Table View sort "Attribute" by "Sort Date".

    Here is sample code...

    let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8irNMTJW0lEyUYrViVZyLE0H88zAPJCcCZBnDJcD8YyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
      #"Added Sort Date" = Table.AddColumn(#"Changed Type", "Sort Date", each Date.FromText([Attribute],[Format="MMMyy"]), type date)
    in
      #"Added Sort Date"

     This yields,

    Hope this helps

      • collinsg's avatar
        collinsg
        Solution Sage
        2 years ago

        Good day mespiritu ,

        The sample code gives this result in the query editor...

        To reproduce this, create a blank query, open the Advanced Editor window and replace everything in it with the sample code.

        The steps of the code work as follows.

        1. "Source" step: Creates a test table with a column "Attribute" with values Jul23, Aug23, Jul24, Aug24 and a column "Values" with values 4, 6, 3 and 2.
        2. "Change type" step: Sets the type of the "Values" column.
        3. "Added Sort Date" step: adds the "Sort Date" column.

        If you wish to apply the 3rd step to your table, add the step to your query by pressing the fx button on the formula bar, paste in

        = Table.AddColumn(#"Changed Type", "Sort Date", each Date.FromText([Attribute],[Format="MMMyy"]), type date)

        and replace #"Changed Type" with the name of the preceding step in your query.

         

        What was the error message?

        Regards

    • mespiritu's avatar
      mespiritu
      Helper I
      2 years ago

      Hi there, I actually used the sample code above in advanced editor, but only receiving Jul23-Aug24 attributes. What is the correct code to get dates from Jul23 to Aug24?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mespiritu 

    After adding the 12 rolling month column, close and apply it. then you can sort the attribute column by the 12 rolling month column in Power BI Desktop. Click the attribuye column, then select sort by the 12 rolling month column , you can refer to the following picture.

    And then sort the rolling month column ascending, then put the field to the visual, it can work.

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mespiritu 

    You can see my solutions, See if it can help you.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.