Sort by Date X - Axis

[SachinC]

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!

[MFelix]

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.

[SachinC]

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!

[Greg_Deckler]

Try replacing the semi-colons in @MFelix 's solution with commas. It varies based on language version of Power BI Desktop.

[SachinC]

Using commas didn't work; this is what doesn't work:  

DateInspection-M-Y = FORMAT([DateInspected],"mm-yyyy")

[MFelix]

You are adding a calculated column correcr?

[SachinC]

Do you mean a 'Measure'? Thanks.

[MFelix]

I'm refering to columns not measures.

Measure have different syntax due to the way they are calculated.

You cannot use measures in axis of visualizations.

[MFelix · Accepted solution]

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")})