Forum Discussion
Sort by Date X - Axis
Hi,
I am connecting to a SQL server instance. I have a date/time field called DateInspected.
I want to use this field but want to convert this date field to mm-yyyy and use this in a bar chart and sort by this date, so 01-2019, 02-2019 ....etc....
I am blowing my brains out. I tried a few suggestions online and in this forum but it doesn't work. I created a new field called DateInspected-M-Y = month([DateInspected])&"-"&year([DateInspected]), but this didn't work when sorting.
Help please!
Thanks!
Hi SachinC ,
Syntax I gave you is for a DAX column you should replace the dot comma by comma (it's regional setting thing).
FORMAT([DateInspected],"mm-yyyy")On the query editor the formula should be:
Text.Combine({Date.ToText([DateInspected], "MM"), "-", Date.ToText([DateInspected], "yyyy")})
8 Replies
Hi SachinC ,
When you are using Month(Table[Column]) this will give you a result of 1 , 2, 3 so when you sort out with the rest of your formula that will give you a text so 10, 11 and 12 will come before 2.
You need to do the following formula:
DateInspected-M-Y = FORMAT([DateInspected];"mm-yyyy")this will return the format 01-2020 and so on and you can sort as you need.
MFelix,
Good shout - that didn't work.
In Edit Query, when I try to create a custom field called DateInspectedNEW, using: FORMAT([DateInspected];"mm-yyyy"), it says: Token Comma expected.
When I create a new field in the Fields list; I get: The syntax for ';' is incorrect. DAX(FORMAT([DateInspected];"mm-yyyy"))).
The original field: DateInspected is of Data type: Date, Format: 13/03/2001 (dd/MM/yyyy)
Please help!
Hi SachinC ,
Syntax I gave you is for a DAX column you should replace the dot comma by comma (it's regional setting thing).
FORMAT([DateInspected],"mm-yyyy")On the query editor the formula should be:
Text.Combine({Date.ToText([DateInspected], "MM"), "-", Date.ToText([DateInspected], "yyyy")})
