Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

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.

Need Help

9 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

       

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

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        5 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Pragati11
        Icon for Super User rankSuper User
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.