Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Pivot chart doesnt sort custom date correctly

Hi experts,

I need the following format on my date "dd/mm/yyyy ddd" (31/12/2021 Sun) so I am using the format function on powerpivot to get the date in that format, however when I do that, my chart doesn't sort the date correctly. See pic below

Correct sorting using standard date format

Incorrect sorting using formatted date

Any suggestion?

Anonymous
5 years ago
Thanks Anonymous , I ended up solving the problem sorting out the column based on another col. which was date type

4 Replies

Greg_Deckler
Community Champion
5 years ago
Anonymous That's because using FORMAT changes the data to Text and not Date. You will need a Sort By column most likely or try using custom format strings. See Chelsie Eiden's Duration. Chelsie Eiden's Duration - Microsoft Power BI Community
- Anonymous
  Not applicable
  5 years ago
  
  Thanks Greg_Deckler , however not sure how I could adjust your suggestion (link) from hours to days. I have tried to change the date format, still using the date format, but not sure why Excel doesn't recognize it. Using Sort by column doesn't work either. I am not using powerbi, just excel.
  - Anonymous
    Not applicable
    5 years ago
    Hi Anonymous ,
    
    The line chart can sort by the date field correctly even though set the format of the date field as "dd/mm/yyyy ddd" just as shown in below screenshot...
    
    In addition, please review the content in the following links, hope they can help you resolve the problem.
    
    Excel Won't Sort Dates Correctly - The Solution!
    
    How to sort by date in Excel: in chronological order, by month, auto sort
    
    Best Regards