Forum Discussion
Sorting string date dd-mmm in line chart
Hi Everyone,
Could you guide me on what is the correct function to sort properly this string date dd-mmm in my line chart
The date goes like this:
Calendar date dd-MMM = FORMAT(DateTable2[Date], "dd-MMM")And the sort colum goes like this:
Sort dd-mmm = DAY(DateTable2[Date]) & MONTH(DateTable2[Date])
My result is obviously not correct:
Hey dustdaniel
Keep the dd-MMM format for display, and then create a proper sorting column using the numeric value of the month and day like this:
SortColumn = MONTH(DateTable2[Date]) * 100 + DAY(DateTable2[Date])This ensures that January 1st (0101) comes before February 1st (0201), and so on.
Don't forget to set the Sort by column in your data model to sort Calendar date dd-MMM by SortColumn.
4 Replies
Hey dustdaniel
Keep the dd-MMM format for display, and then create a proper sorting column using the numeric value of the month and day like this:
SortColumn = MONTH(DateTable2[Date]) * 100 + DAY(DateTable2[Date])This ensures that January 1st (0101) comes before February 1st (0201), and so on.
Don't forget to set the Sort by column in your data model to sort Calendar date dd-MMM by SortColumn.
Thank you very much Marcel,
It's working propperly. I have an additional question.
When I have a Line chart with only one continuous line, it is available the option X-axis / Type: contonuous but this is not available then there are multiple lines. Is there a tourn around for that?
Ensure that your x-axis data is numerical or date/time...
