Forum Discussion

clteh9's avatar
clteh9
Helper I
2 years ago
Solved

Sorting chart by date

'DAY' will be displayed as X-axis in a chart but the 'DAY' is not sorted by date correctly. 

DAY = format(table1(EXIT_DT],"dd/mm/yyyy")

 

Current display: 01/04/2023, 01/05/2023, 01/06/2023.....

Expected display: 01/04/2023, 02/04/2023, 03/04/2023.......

 

I would like to seek guidance on how to sort the chart by date correctly. 

thanks in advance for the help

 

 
Need Help

8 Replies

  • HarishKM's avatar
    HarishKM
    Super User
    2 years ago

    clteh9 Hello,
    I will suggest  create a date table like.


    Step 2: Select all column then unpivot it 
    expected output will like this.


    Step 3 : Close and apply your query then drag it to slicer and expect output like this.


    Enjoy.

    Thanks
    Harish M
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

     

     



  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    You should create a Calendar Table with a relationship (Many to One and Single) from the Exit Date to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.

    • clteh9's avatar
      clteh9
      Helper I
      2 years ago

      Actually i have 3 new columns created in order to toggle the x-axis display between DAY, MONTH and YEAR
      DAY = format(table1(EXIT_DT],"dd/mm/yyyy")
      MONTH = format(table1(EXIT_DT],"mm/yyyy")
      YEAR = format(table1(EXIT_DT],"yyyy")

      if DAY selected,expected display is 01/04/2023, 02/04/2023, 03/04/2023.......
      if MONTH selected, expected display is 04/2023, 05/2023, 06/2023/.....
      if YEAR selected, expected display is 2022, 2023

      By creating calendar table with relationship, can the x-axis be sorted accordingly?
      Possible to provide a simple pbix for reference? Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        Share data in a format that can be pasted in an MS Excel file.

  • KEAlexander's avatar
    KEAlexander
    Helper I
    2 years ago

    It looks like it is an issue with the regional settings. In some countries the convention is that the day comes first, in other countries the month comes first.  You've specified a format with the day first, but the regional settings are causing sorting assuming that the month is first. So changing the regional settings in PBI and/or on your computer might fix the issue. 

    If this has helped, please give it a thumbs up.