Sorting two date columns

[bo_afk]

In my data I have two date columns, one is RaisedOrder date and the other is DeliveryDate.

I've created a slicer (using the hierarchy slicer) for each of these columns by Year and Month name. Currently, the slicer is sorted by month name alphabetically, (e.g April at the top). 

I would like to sort this by month number so that January is at the top and December at the bottom. And I'd like to do this for both slicers for RaisedOrder date and DeliveryDate.

Any ideas?

Thanks

afk

[apentyala]

Create a Date table and sort month name using month number. maybe it will useful

https://www.youtube.com/watch?v=cnH6oMhWKOE

Regards,

[amitchandak]

Create a month Sort column and mark it as sort column

sort Month = format(date[date],"YYYYMM")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[bo_afk]

Hi @amitchandak , thanks for your suggestion. But when I try to do this, it gives me the error message "this column can't be sorted by a column that is already sorted, directly or indirectly, by column".

I don't remember sorting any other columns by this...but is it possible for me to double-check this?

[amitchandak]

Please make sure the sort column is not based on month-year column. Both of them can be based on the same base column-like Date.

[bo_afk]

Hi @amitchandak , can you go through the steps again please? It doesn't seem to have worked for me.

I have the following columns:

RaisedDate	RaisedYear	RaisedMonth	DeliveryDate	DeliveryYear	DeliveryMonth	Quantity
2020-01-03	2020	January	2020-03-14	2020	March	250
2020-01-06	2020	January	2020-05-05	2020	May	100

When I create a hierarchy slicer for DeliveryYear and Month, I get the following.

[V-lianl-msft]

Hi @bo_afk ,

There is a visual called "hierarchy slicer" in marketplace. Using this visual and applying the date hierarchy provided by powerbi can meet your needs.

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

[bo_afk]

Hi @V-lianl-msft , thanks for your feedback. Initially I used this slicer but it was adding in extra years and months that didn't exist in my data so I was unable to use it. 

[V-lianl-msft]

Hi @bo_afk ,

Is this problem sloved?
If not, please let me know.

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

[bo_afk · Accepted solution]

Hi @V-lianl-msft , I solved it by bringing in the month number directly from the data source, along with the month name. I could then sort the month name by the month number and this looked to solve the issue.

Initially I'd created a month number column within Power BI, based off of the date, and then sort the month name by this column but this didn't work.

[V-lianl-msft]

Hi @bo_afk ,

You need to sort "DeliveryMonth" by "DeliveryDate"

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