Sorting chart by date

[clteh9]

'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

[Ashish_Mathur · Accepted solution]

Hi,

Refer to the attached PBI file.

[KEAlexander]

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. 

[HarishKM]

@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]

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]

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]

Hi,

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

[clteh9]

item#|category|sub category|start time|end time
1|A|1|2022-01-02 13:00:00|2022-01-02 15:00:00
2|A|2|2022-01-02 13:00:00|2022-01-02 15:00:00
3|A|2|2022-04-02 13:00:00|2022-04-02 15:00:00
4|A|1|2022-05-02 13:00:00|2022-05-02 15:00:00
5|A|3|2022-06-02 13:00:00|2022-06-02 15:00:00
6|B|1|2022-07-02 13:00:00|2022-07-02 15:00:00
7|B|2|2022-12-02 13:00:00|2022-12-02 15:00:00
8|B|1|2022-12-03 13:00:00|2022-12-03 15:00:00
9|B|1|2023-01-04 13:00:00|2023-01-04 15:00:00
10|A|1|2023-01-05 13:00:00|2023-01-05 15:00:00
11|A|2|2023-01-06 13:00:00|2023-01-06 15:00:00
12|A|2|2023-01-07 13:00:00|2023-01-07 15:00:00
13|A|1|2023-01-08 13:00:00|2023-01-08 15:00:00
14|A|3|2023-01-09 13:00:00|2023-01-09 15:00:00
15|B|1|2023-01-10 13:00:00|2023-01-10 15:00:00
16|B|2|2023-01-10 13:00:00|2023-01-10 15:00:00
17|B|1|2023-02-12 13:00:00|2023-02-12 15:00:00
18|B|1|2023-02-13 13:00:00|2023-02-13 15:00:00
19|A|1|2023-02-14 13:00:00|2023-02-14 15:00:00
20|A|2|2023-02-15 13:00:00|2023-02-15 15:00:00
21|A|2|2023-02-16 13:00:00|2023-02-16 15:00:00
22|A|1|2023-02-17 13:00:00|2023-02-17 15:00:00
23|A|3|2023-02-18 13:00:00|2023-02-18 15:00:00
24|B|1|2023-03-20 13:00:00|2023-03-20 15:00:00
25|B|2|2023-03-21 13:00:00|2023-03-21 15:00:00
26|B|1|2023-03-22 13:00:00|2023-03-22 15:00:00
27|B|1|2023-03-23 13:00:00|2023-03-23 15:00:00
28|A|1|2023-03-24 13:00:00|2023-03-24 15:00:00
29|A|2|2023-03-25 13:00:00|2023-03-25 15:00:00
30|A|2|2023-08-15 13:00:00|2023-08-15 15:00:00
31|A|1|2023-08-16 13:00:00|2023-08-16 15:00:00
32|A|3|2023-08-17 13:00:00|2023-08-17 15:00:00
33|B|1|2023-08-18 13:00:00|2023-08-18 15:00:00
34|B|2|2023-08-19 13:00:00|2023-08-19 15:00:00
35|B|1|2023-08-20 13:00:00|2023-08-20 15:00:00
36|B|1|2023-08-21 13:00:00|2023-08-21 15:00:00
37|A|1|2023-08-22 13:00:00|2023-08-22 15:00:00
38|B|2|2023-08-23 13:00:00|2023-08-23 15:00:00
39|A|2|2023-08-24 13:00:00|2023-08-24 15:00:00
40|B|1|2023-08-25 13:00:00|2023-08-25 15:00:00

[Ashish_Mathur · Accepted solution]

Hi,

Refer to the attached PBI file.

[clteh9 · Accepted solution]

To add on

- to sort 'Date name' by 'Date number' and sort 'Month name' by 'Month number'