How to sort chart by 3 date columns?

[jaryszek]

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

[MasonMA]

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]

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 · Accepted solution]

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.

[jaryszek]

thanks!