Forum Discussion

jaryszek's avatar
jaryszek
Super User
10 months ago
Solved

How to sort chart by 3 date columns?

Hello,

I have model like that:
 

 

and I am using Dates columns here to create date hieararchy:


And now i got not ordered items in visual after adding date hierarchy with sum of costs:


i tried to use custom sort order from Column tools for each Hierarchy Item but it is failing:


I am trying to use DateKey (integer) as sort column but it is complaining. 
What is the best practice to solve the case? 

Attaching model from my google disc - will be easier:

https://drive.google.com/file/d/13wn-vSyg5q2mvU5KtWzMSiPZWHDHGxVR/view?usp=sharing


Best,
Jacek

 

 

 

 

Need Help
  • MasonMA's avatar
    MasonMA
    10 months ago

    Hi Jacek, the MonthName is of Text data type. By default, Power BI sorts text alphabetically (Apr, Aug...) so it's why we sort this column by a numeric column.

     

    While Day and Year are both stored as Whole Number. Numbers naturally sort in ascending order (1, 2, 3… or 2023, 2024…), so Power BI doesn’t need a helper column.

     

4 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    10 months ago

    Could you try first changing your Data Type for 'Day' and 'Year' Column from 'Text' to 'Number' under table view? then for the visual you either can add 'Date' to tooltip and sort axis by Date in Tooltip, or go back to Table view and sort by 'MonthName' column by 'MonthNum'. I think either way will give you correct date order on visual. 

     

     

    • jaryszek's avatar
      jaryszek
      Super User
      10 months ago

      thanks. 

      I tried both methods and they worked. 

      Can you explain how this "go back to Table view and sort by 'MonthName' column by 'MonthNum'" is working ?

      Why Days and Years accordingly adjusted? 

      Best,
      Jacek

      • MasonMA's avatar
        MasonMA
        Super User
        10 months ago

        Hi Jacek, the MonthName is of Text data type. By default, Power BI sorts text alphabetically (Apr, Aug...) so it's why we sort this column by a numeric column.

         

        While Day and Year are both stored as Whole Number. Numbers naturally sort in ascending order (1, 2, 3… or 2023, 2024…), so Power BI doesn’t need a helper column.