Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Index Question/ Issue

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.month snip.JPG

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.

9 REPLIES 9
v-eqin-msft
Community Support
Community Support

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:

1.14.3.1.jpg

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

1.14.3.2.PNG

The final output is shown below:

1.14.3.3.PNG

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
Not applicable

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.

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:

1.14.3.4.jpg

Finally, the Mon in Matrix will be sorted correctly.

1.14.3.5.PNG

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
Not applicable

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

Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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?

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

relanip.JPGmonsnip.JPG

 

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

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.