Forum Discussion
Sort Custom Date Column in Array
Hi Syndicate_Admin,
Thank you for sharing the sample data.
Here I Created a Month Order table, establish a relationship with the main data table, and replace the existing month field in the matrix visual with the new one from the Month Order table.i'm sharing the Measure:
MonthOrderTable =DATATABLE("Mes", STRING,"FiscalMonthOrder", INTEGER,"MonthNumber", INTEGER,{{"abril", 1, 4},{"mayo", 2, 5},{"junio", 3, 6},{"julio", 4, 7},{"agosto", 5, 8},{"septiembre", 6, 9},{"octubre", 7, 10},{"noviembre", 8, 11},{"diciembre", 9, 12},{"enero", 10, 1},{"febrero", 11, 2},{"marzo", 12, 3}})
I'm sharing the .pbix file for the reference.If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you for using Microsoft Fabric Community Forum.
Hi Syndicate_Admin,
Thank you for sharing the sample data.
Here I Created a Month Order table, establish a relationship with the main data table, and replace the existing month field in the matrix visual with the new one from the Month Order table.
MonthOrderTable =
I'm sharing the .pbix file for the reference.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you for using Microsoft Fabric Community Forum.
Thank you very much for the solution.
I ask you one last question, why did you need to do all that to order it and it didn't work to sort the date column by another ordered column??
Thank you very much again
Hi Syndicate_Admin,
That’s a great follow-up question and it’s one that often confuses many Power BI users, especially when working with fiscal calendars.
You tried to create a custom column assigning April = 1, May = 2, and so on, up to March = 12. Then, you used the "Sort by Column" feature to sort your date or month column based on this new custom column. You expected the matrix visual to reflect this new order accordingly. This approach has a limitation.When you apply "Sort by Column" in Power BI, it expects a one-to-one relationship between the column being sorted and the column used for sorting. If you're trying to sort a date column, which includes multiple entries for April, May, and so on using a custom column that only contains 12 unique values (like April = 1, May = 2, etc.), Power BI cannot resolve the many-to-one relationship. This is because there are multiple rows for April 2023, April 2024, and so on, but only a single value (1) assigned for April in the sort column. As a result, the sort operation fails.
By creating a separate Month Order table, you can isolate the month names (e.g., "abril", "mayo") and assign a corresponding fiscal order to them, such as April = 1, May = 2, and so on. This Month Order table acts as a dimension table, while your main dataset functions as the fact table. A relationship is then established between the two tables using the month name as the common field.
In your matrix visual, you use the month from the Month Order table, which follows a clearly defined sort order based on the FiscalMonthOrder column. This approach aligns with the star schema model, which is considered a best practice in Power BI for maintaining data integrity and improving report performance.If this post was helpful, please consider marking Accept as solution and give us Kudos to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
Thank you for using Microsoft Fabric Comunity Forum.
Thank you very much for the explanation, you were very clear.
Best regards