Forum Discussion

sdhn's avatar
sdhn
Responsive Resident
4 years ago
Solved

Sort Months o

Hi All,   I have multiple charts on dashboard.  I want to sort by month Start from July to end to June.  July, Aug, Sept, Oct, Nov, Cec, Jan, Feb, Mar, April, May , June   I have clusterd colum...
Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago

    I advise and Ashish, others also mentioned the same above. (separate date table concept)

     

    a) Create a table for our needs i.e., simple month table "Month Names" 

     

    The table is static and create as using enter data

     

    The table data has always only 12 rows i.e., month names. The names are the same values in the Transaction table.  January, February ...

     

    Table: "Month Names"

     

    b) Create "DisplayMonthSort" in the transaction table. Which Ashish is called as Month Order column. Steps are

     

    Create relationship between "Month Names" and your Tx table "Mail V..."

     

    Bring the column "DisplayMonthSort" to your transaction table

     

     

    DisplayMonthSort = related('Month Names'[Display Month Sort])

     

     

    and do the sort order like we talked above.

     

    Try the other steps like Sort by column, hide in report view ...

     

     

    See if this works

     

    Note: Sample mockup data .pbix file always helps  

    Thanks

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Assuming the Date column in the Calendar Table has genuine dates, write these calculated column formulas:

Month number = month(Calendar[date])

Month name = format(calendar[date],"mmmm")

Financial Year = if(calendar[month number]>=7,year(calendar[date])&"-"&year(calendar[date])+1,year(calendar[date])-1&"-"&year(calendar[date]))

Create another 2 column table with Month name and Month order (name this table as month_order).  In the Month Order column, July will be 1, August will be 2 and so on - June will be 12.  Create a relationship between the Month name column of the Calendar Table with the Month name column of the month_order table.  In the Calendar table, write this calculated column formula to get the order column from the month_order table

Month order = related('month_order'[order])

In the Calendar table, sort the Month name column by the Month order column.  To your visual/slicer, drag Year, Month name from the Calendar Table.