Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago
Solved

Sort Custom Date Column in Array

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 ...
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

v-sgandrathi's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
1 year ago

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.