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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
Memorable Member
Memorable Member

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.