Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
markmess77
Resolver I
Resolver I

How to change date format in my custom measure?

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

measure.PNG

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

view.PNG

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?

1 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])

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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/

@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

date.PNG

@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])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors