How to change date format in my custom measure?

[markmess77]

I have a measure I wrote to conditionally format a text box based on which filter options are currently selected.

This works fine, with the exception that the month and quarter information does not display as I would expect.

In the example above, I would expect the output of the conditionally formatted text box to read: Jan 2019, Apr 2019, Jan 2020, Feb, 2020. However, it is leaving out the year and grouping same-name months together (i.e. January in this case). I also have this same issue when selecting by quarter.

I was under the impression that FORMAT(value, "MMM yyyy") and the quarter eqivalent of FORMAT(value, "q yyyy") would result in my desired output. Is there something that I am missing?

[amitchandak]

You create a new column in your date table in the required format and use Hierarchical slicer from feb release

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2020-feature-summary/

[markmess77]

@amitchandak I have a table of distinct dates in the format that I would like already. However this is by month, but my desired result would be for this to apply by quarter as well. I assumed that implementing the format function would accomplished this

[amitchandak · Accepted solution]

@markmess77 , Create a month table like this have Qtr -year and other build in that

MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])