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

[Sharingiswonder]

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.

[hnguy71]

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.

[Sharingiswonder]

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

[hnguy71 · 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

Change Header options:

hide date_Sort: