How to Sort a Custom Date Column

[ReemAlashhab]

Hi Everyone , I have a date table that has  : date , year , month , quarter and ect. 

In addition, there is a new column I created it's called Week. The format for this column is : ( W + Week Number  + Month Name + date of the first day of the week ) Ex : W1-Jan1.

I have did some visuals using week column , and the results were not ordered . What I did  is that I used date column as a tooltip and ordered the week column using the tooltip. However, when I create a slicer , I'm not able to do that. I tried to click on the week column and then sort by column , but it shows me an error message that I can't sort by date. 

I also tried to download some other kind of slicers , but they had issues because I want to use a dropdown list slicer.

So what should I do ?

[v-jianboli-msft · Accepted solution]

Hi @ReemAlashhab ,

Approve with @Avantika-Thakur , please try:

Create a new column weeknum:

Then use sort by column, choose weeknum:

Final output:

Best Regards,

Jianbo Li

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

[ReemAlashhab]

@Avantika-Thakur Can you show me the solution using screenshots and forulams ?

[amitchandak]

@ReemAlashhab , Create a week start date

//Monday week

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

//Sunday week

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1

And sort on that

any other week start

.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[ReemAlashhab]

I tried to do that, but it doesn't work

[Avantika-Thakur]

Hi @ReemAlashhab .

You can try creating a lookup for week values and a column with Sort IDs.

Connect the main Week column with the Lookup week column and use the Week column from lookup in your visuals and then sort the week with Sort ID column. This should solve your purpose.

Thanks,

Avantika