The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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")})
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
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!
Try replacing the semi-colons in @MFelix 's solution with commas. It varies based on language version of Power BI Desktop.
Using commas didn't work; this is what doesn't work:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDo you mean a 'Measure'? Thanks.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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")})
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |