Forum Discussion

SachinC's avatar
SachinC
Helper V
6 years ago
Solved

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 ...
General Comment
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    6 years ago

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

     

     

SachinC's avatar
SachinC
Helper V
6 years ago

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!

 

MFelix's avatar
MFelix
Super User
6 years ago

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