How to sort date column(have multiple same value)

[YunJ]

Hi,

I'm wondering is there any method to sort "Date Range" column?

Please kindly find the pbix file:

https://www.dropbox.com/s/s9da9ce3jimw1ko/Date.pbix?dl=0

[MFelix · Accepted solution]

Hi @YunJ ,

I would replace that column by another one see below codes for DAX and Power query:

Power Query:

Date.ToText([Start], "yyyy/MM/dd") & "-" &Date.ToText([End], "yyyy/MM/dd")

DAX:

FORMAT(Sheet2[Start];"yyyy/mm/dd") & "-"&FORMAT(Sheet2[End];"yyyy/mm/dd")

This will give you the dates with double digits in month and day so the order should be correct.

[MFelix · Accepted solution]

Hi @YunJ,

You need to have double digits on both day and month in order to get 01-01 otherwise the order will be incorrect change your format from M/D to MM/DD should do the trick

[MFelix · Accepted solution]

Hi @YunJ ,

I would replace that column by another one see below codes for DAX and Power query:

Power Query:

Date.ToText([Start], "yyyy/MM/dd") & "-" &Date.ToText([End], "yyyy/MM/dd")

DAX:

FORMAT(Sheet2[Start];"yyyy/mm/dd") & "-"&FORMAT(Sheet2[End];"yyyy/mm/dd")

This will give you the dates with double digits in month and day so the order should be correct.

[YunJ]

Hi @MFelix 

I wrote the DAX as following:

But the x-axis order is not in the correct sort.

Thanks for your time.

[v-xuding-msft · Accepted solution]

Hi @YunJ ,

Please try like this:

Rank Start = RANKX(Sheet2,Sheet2[Start],,ASC,Skip)

 

[MFelix · Accepted solution]

Hi @YunJ,

You need to have double digits on both day and month in order to get 01-01 otherwise the order will be incorrect change your format from M/D to MM/DD should do the trick

[YunJ]

Hi @MFelix Thanks a lot for your solution!