Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |