Reply
Sharingiswonder
Microsoft Employee
Microsoft Employee

to convert a date to a format 'dd-mmm-yyyy' and should be able to sort the field in powerbi

Hi,

 

Is there a way we could bring in a field to a format 'dd-mmm-yyyy' and still be able to sort. (I understand the Format function will help me with the format but it wouldnt let me sort as it would it be a text.)

 

Thanks in Advance.

 

 

 

 

1 ACCEPTED SOLUTION

Sorry. I'm not quite sure I understand you.

 

These two are calculated columns:

format_Date = FORMAT(Query1[Date], "dd-mmm-yyyy")

date_Sort = RANKX(Query1, [Date],,ASC, Dense)

Sort Ascending by date_Sort

before_sort.png

 

Change Header options:

header_options.png

 

hide date_Sort:

hidden_sort.png



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

3 REPLIES 3
hnguy71
Super User
Super User

You can use this method:

 

In a calculated column:

date_Sort = RANKX(Query1, [Date],,ASC, Dense)

Next, put your formatted date and the sort into a visual.  In the Format Property under Column Headers, turn both off "Auto-size column width" and "Word Wrap". Sort your date and then "hide" your date_Sort field.

 

rank_sort.png



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Thank you for your suggestion. There is one issue .. the RANKX function would order text output ( which is from the format function for'dd-mmm-yyyy') only in the alphabetical order first..

example  (choose ascending)

05-Apr-2016

05-Apr-2017

05-Aug-2016

05-Aug-2017

06-Apr-2018

06-Aug-2018

30-Dec-2016

Sorry. I'm not quite sure I understand you.

 

These two are calculated columns:

format_Date = FORMAT(Query1[Date], "dd-mmm-yyyy")

date_Sort = RANKX(Query1, [Date],,ASC, Dense)

Sort Ascending by date_Sort

before_sort.png

 

Change Header options:

header_options.png

 

hide date_Sort:

hidden_sort.png



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)