Forum Discussion
How to Sort a Custom Date Column
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 ?
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.
5 Replies
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
Avantika-Thakur Can you show me the solution using screenshots and forulams ?
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-Power-BI-Turning/ba-p/1187482I tried to do that, but it doesn't work
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.
