cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Date format and Sorting in a Matrix Visualization

Hello,

 

I guess part of this is feedback on the default formats for date. If I want to display the month and year, there is only the option for the long month and year option (mmmm yyyy) and not the ability to have a short value (mmm yy). I don't understand why there aren't more options - at least Excel has that short option.

 

Now, I know I can create a column or measure to do FORMAT([], "mmm yy"). The issue here is that the resulting value is text, so when I attempt to sort on date, it sorts as alphabetical value. I get that I could create some sort of sort column, or keep the actual date column as part of the visual, 'hide' the column and sort by that value. However, this doesn't seem to work when using the Matrix visualization. 

 

I'm kind of at a loss on this one, I'm betting I missed something obvious.

 

Thanks

 

1 ACCEPTED SOLUTION

I'm sorry, but your answer doesn't make any sense to me. It's probably a conceptual problem on my end. There is not a date table in the model.

 

The solution I found was to go into the data tab, highlight the Display column (where I did the format command) and choose "Sort By" and then picked the column with the date value.

 

 

 

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@TMOCostanzo 

If I understood your question well, you should be able to create Mmm YYYY date column and sort it in the propper order right?
Try to add the following date table. You can create "MonthYearNo" column to sort the "Month Year" column.

Dates = 
ADDCOLUMNS(
    CALENDAR("01/01/2020","31/12/2020"),
    "MonthYearNo", YEAR([Date])*100  + MONTH([Date]),
    "Month Year", FORMAT([Date],"MMM YYYY")
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I'm sorry, but your answer doesn't make any sense to me. It's probably a conceptual problem on my end. There is not a date table in the model.

 

The solution I found was to go into the data tab, highlight the Display column (where I did the format command) and choose "Sort By" and then picked the column with the date value.

 

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors