Sort Months o

[sdhn]

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 column charts, ribbion chart and blue chart by okviz.  Thanks 

[sdhn]

sevenhills 

c)  In the model, select the month column, and click on sort by column and use the "DisplayMonthSort"

encoutering error here 

[sevenhills · Accepted solution]

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

[sdhn]

How to upload file?  I will post with sample data. First I will try your instructions.  Thanks 

[sdhn]

c)  In the model, select the month column, and click on sort by column and use the "DisplayMonthSort"

encoutering error here 

[Ashish_Mathur]

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.

[mahoneypat]

You can use a column expression like this one, and then use the new column to Sort By on your month column.

FiscalMonthSort = var monthnum = MONTH('Date'[Date])

return if(monthnum>=7, monthnum - 6, monthnum + 6)

Pat

[sevenhills]

a) In the date or transaction table, create a calculated column, say as "DisplayMonthSort", and values for July as 1, Aug as 2, ... June as 12

Idea is same as fiscal month display.

b) In the model, hide this column in the report view i.e., using "Hide in report view"

c)  In the model, select the month column, and click on sort by column and use the "DisplayMonthSort"

d) Create visualizations will give the same effect.

If you already have visualizations, it should automatically apply the changes. If not, save, close and open.

🙂 

Sample screens to give an idea

dummy data:

hide in report view: 

sort by column: 

duumy data visualization:

[sdhn]

Thanks for your resposne., how to create transaction table?  

[sevenhills]

What I meant is in your data table AKA transaction table. You can create a column and hide it. 

DisplayMonthSort = 
var _m = Month('Table'[DateCol])
var _startMonth = 7 -- July
return If( _m >= _startMonth, _m - 6, _m + 6)

If your table is huge, then you can do the same behavior in the date table and link to it and use the date table's month.

[sdhn]

I am getting error beacuse my date column is using TEXT datatype. I am trying to change datatype but not succesfull yet. any suggestion 

[sevenhills]

Please can you post the screenshot, remove the personal/identifier data

[sdhn]

I am trying to create  caluclated colum in the existing table.

[sevenhills]

The syntax I provided is based on "date" data type column. Since you have text and without knowing full details, let us do this way...

DisplayMonthSort = Switch(
'MailVolumFY1'[FY 2020-21], 
"July", 1, 
"Aug", 2, 
"Sep", 3, 
"Oct", 4, 
"Nov", 5, 
"Dec", 6, 
"Jan", 7, 
"Feb", 8, 
"Mar", 9, 
"Apr", 10, 
"May", 11, 
"Jun", 12
, 1)

By default , this column data type will be Whole Number. (verify)

[sdhn]

I am able to create calculated column.  I hide new column on report view. I click on ... top let of graph but do not see this new calculated column

[sdhn]

Cannot convert value 'July' of type Text to type Date.