Index Question/ Issue

[Anonymous]

Hi, my data is in a table called Ex Table.  I have another table called MonthOrder that is indexed so my dates are in order.  The dates are in corrrect ordere in my graph.  I want to add a data table, but want the months in "mmm" format, rather than the "mmm yyyy" that the graph is in.

I have tried creating a second table with months as Sep, Oct etc and indexing it, but when i use it, it doesnt change the month order, they are still in alphabetical order.

[v-eqin-msft]

Hi @Anonymous ,

According to my understanding, you want to display the Date(MMM YYYY format) with correct order, right?

Please follow these steps:

1. Use the following formula to create a new table

Table =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( "2018/9/1", "2020/9/1" ),
            "Year Month", FORMAT ( [Date], "MMM YYYY" ),
            "Month", FORMAT ( [Date], "MMM" ),
            "YearMonth Number",
                YEAR ( [Date] ) * 100
                    + MONTH ( [Date] )
        ),
        "Year Month", [Year Month],
        "YearMonth Number", [YearMonth Number]
    )
)

2. Click the Sort by column tab to sort the Year Month column by YearMonth Number column:

3. Build relationship between the two tables based on Month and YearMonth:

The final output is shown below:

Please kindly take a look at the pbox file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry no. I have the mmm yyyy in order, as shown by the bar graph in the image I attached.  I achieved this by using a separate table with an index.  The issue I have is with the data table above the graph, where the months are in alphabetical order.

[v-eqin-msft]

Hi @Anonymous ,

Sorry for my misunderstanding.You could follow these steps to add a Month Number column and then sort by it.

1.Add a Date column

Date =
DATEVALUE ( [Month] )

2. Extract Month Number:

Month Number =
MONTH ( [Date] )

3. Use "Sort by column" feature:

Finally, the Mon in Matrix will be sorted correctly.

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Again apologies, but in my comment I wrote I need it to go by academic year, so September through to August.

[Pragati11]

Hi @Anonymous ,

I would say create a CALENDAR date table and ten create your MMM month column in this new calendar table. Create relationships between your other tables and Calendar table and use MMM column for your reporting from this new CALENDAR table. The months will be sorted in correct order.

Refer following article on how to create this DATE table in Power BI:

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Thanks,

Pragati

[Anonymous]

Sorry, something that I have failed to mention.  I need it by academic year, so the year goes from Sep to Aug.

Will your solution still work?

[Pragati11]

Hi @Anonymous ,

I don't have your power bi file infront of me, so I am guessing the solutions right now. Just try and see. Do you have a flag in your data somewhere specifying your academic year?

Date tables are Calendar tables they don't by default take academic year like you are mentioning. You will have to add some flag in DATE table or in your data to define this.

Thanks,

Pragati

[Anonymous]

done this, but still isn't working. Unless I've done something wrong?

[Pragati11]

HI @Anonymous ,

1. First issue is your many-to-many relationship.
2. Create a relationship on DATE column rather than MONTH column and test again.

Thanks,

Pragati