Sort Custom Date Column in Array

[Syndicate_Admin]

Good morning

I want to make a matrix where the data of a date column ordered by fiscal year is shown, that is, the first month is April, May, June, etc.

I tried sorting the date column by a custom column that assigned the month of April the number 1, May the number 2, etc. But it didn't work for me

I leave you a screenshot of the info, the matrix below should be ordered according to the columns above.

The order of the date column was like this:

But as you will see, it does not work.

Best regards

[Syndicate_Admin]

I leave the file at the following address:

OA.pbix

Thanks a lot

Best regards

[v-sgandrathi · Accepted solution]

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.

[Syndicate_Admin]

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

[v-sgandrathi]

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.

[Syndicate_Admin]

Thank you very much for the explanation, you were very clear.

Best regards

[Syndicate_Admin]

Thank you so much for the reply, but it didn't work for me.

The Order column is set as an integer and the date column is set as a date, I sort it according to the Order column, but it doesn't work for me.

I leave a screenshot, (how can I attach the pbix file?)

Best regards

[v-sgandrathi]

Hi @Syndicate_Admin,

To Attach the Pbix.file just go to the reply option and click on it you will see the browse option as below screenshot. 
Here you can attach the file.

Thank you.

[Syndicate_Admin]

I don't have that option...

[v-sgandrathi]

Hi @Syndicate_Admin,

I think you don't have this option.

I'm provding the link you can refer:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

You can upload your data into googledrive or drop box and you can paste the link here.

Thank you.

[v-sgandrathi]

Hi @Syndicate_Admin,

Thank you for reaching out with your question. It sounds like you're trying to sort a date column in your matrix visual based on the fiscal year, starting from April. You're on the right track by creating a custom column that assigns numbers to each month. To make this work effectively in Power BI, you'll need to ensure that your original date column is sorted by this custom column. You can do this by selecting the date column, going to the "Modeling" tab, and choosing "Sort by Column," then selecting your custom fiscal month number column.

Make sure that your custom column has no blanks and correctly matches the months from your date column, and also check that both columns have the appropriate data types, your date column should be of type "Date," and the custom sort column should be "Whole Number." Once sorted, your matrix visual should reflect the correct fiscal month order. 

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.

[BIswajit_Das]

Hello @Syndicate_Admin 
As per you use case If you're using only month values then you can just change your column value format like below attached image and order the Fecha column by Orden column
Note : You should change the Orden column datatype to Whole Number

Thanks & Regard...