Sorting for date table

[RajK2]

I've created custom date table and need to make sorting column by orders via DAX formula, please suggest

example : year 2024,2025 

I've created manual column as i would need custom column in date table.

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=1),1,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=2),2,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=3),3,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=4),4,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=5),5,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=6),6,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=7),7,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=8),8,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=9),9,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=10),10,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=11),11,

AND('Calendar'[Year]=2023,'Calendar'[Month Number]=12),12,

AND('Calendar'[Year]=2024,'Calendar'[Month Number]=1),13,

AND('Calendar'[Year]=2024,'Calendar'[Month Number]=2),14,

AND('Calendar'[Year]=2024,'Calendar'[Month Number]=3),15,

AND('Calendar'[Year]=2024,'Calendar'[Month Number]=4),16,

AND('Calendar'[Year]=2024,'Calendar'[Month Number]=5),17,

YYYY-MM	Sorting
2024-Apr	4
2024-Aug	8
2024-Dec	12
2024-Feb	2
2024-Jan	1
2024-Jul	7
2024-Jun	6
2024-Mar	3
2024-May	5
2024-Nov	11
2024-Oct	10
2024-Sep	9
2025-Apr	4
2025-Feb	2
2025-Jan	1
2025-Mar	3
2025-May	5

[rohit1991 · Accepted solution]

Hi @RajK2 ,
To create a custom sorting column in your manual date table that ensures proper chronological order across years and months, the most effective approach is to generate a continuous numeric sort key using a DAX formula that combines the year and month into a single value. Rather than writing multiple AND conditions, you can simplify your logic by creating a calculated column like this: Sort Order = 'Calendar'[Year] * 100 + 'Calendar'[Month Number]. This will give you values like 202401, 202402, ..., 202512, which naturally sort in the correct year-month order. You can then use this Sort Order column to sort your custom YYYY-MM column by going to the "Modeling" tab and choosing "Sort by Column." This approach is cleaner, scalable for future years, and eliminates the need for repetitive manual conditions.

[Anonymous · Accepted solution]

Hi @RajK2,

Thank you for reaching out in Microsoft Community Forum.

Thank you @rohit1991    for the helpful response.

As suggested by rohit1991,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[rohit1991 · Accepted solution]

Hi @RajK2 ,
To create a custom sorting column in your manual date table that ensures proper chronological order across years and months, the most effective approach is to generate a continuous numeric sort key using a DAX formula that combines the year and month into a single value. Rather than writing multiple AND conditions, you can simplify your logic by creating a calculated column like this: Sort Order = 'Calendar'[Year] * 100 + 'Calendar'[Month Number]. This will give you values like 202401, 202402, ..., 202512, which naturally sort in the correct year-month order. You can then use this Sort Order column to sort your custom YYYY-MM column by going to the "Modeling" tab and choosing "Sort by Column." This approach is cleaner, scalable for future years, and eliminates the need for repetitive manual conditions.