Use special formatted column as abciss

[Anonymous]

Hello, I met a new issue with my Power BI report. I have a special column in one of my field: 

YEAR('FS Table'[UTC Data Load Date]) & " W" & WEEKNUM('FS Table'[UTC Data Load Date], 21)

 I set up an incremental refresh and it is working as intended (refresh and increment my dashboard every week).

But I met an issue, the output format of this column is for exemple: 2021 W23, which is intended, but when the refresh add to the column: 2021 W24, it is wrongly sorted. Week 23 is after week 24, like in picture below:

Is there a method to have a special text field as x abciss and sort it by the last digit or by the last value added?

I know that I can use the weeknumber as number and fix this issue, but I would like to keep my special text....

Thank you for your time, have a good day!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You need to create a "sort by column" for this column.

y_week_sort = YEAR(Sheet1[Date])*100+WEEKNUM(Sheet1[Date],21)

This will keep your custom sort after each refresh.

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

[amitchandak]

@Anonymous , This format should sort in text format. I doubt it is sorted on value, not on the axis. Check on the three dots option to sort and correct the column