Sort By Column Not Working / Help with Dates Table

[tlwest86]

I created a Matrix Visualization to display progress over my Agency's fiscal year, which starts in October (seen in the first screenshot).  I used a date table (seen in the 2nd screenshot) which I have used several times in the past, but it is no loner working as expected.  I previously achieved this by creating a column called 'Fiscal Year' that assigned October as 1, November as 2, December as 3, and so forth.  I then clicked the 'Sort By Column' button and selected the 'Fiscal Year' column (also 2nd screenshot).  I want the columns to be arranged by the 'Fiscal Year' column, which means the columns should be Oct, Nov, Dec, then Jan, but they are sorted alphbetically. 

I know my quarter column is not coded right so I could use some help there as well.  I put the code of the Dates table at the bottom.  Also, is there a way to add weeks to the my dates table?

My dates table looks like this:

Dates =

VAR BaseCalendar =

CALENDARAUTO(9)

RETURN

GENERATE (

BaseCalendar,

VAR BaseDate = [DATE]

VAR YearDate = YEAR (BASEDATE)

VAR MonthNumber = MONTH (BASEDATE)

VAR Quarter = QUARTER(BASEDATE)

RETURN ROW (

"Day", BaseDate,

"Year", YearDate,

"Month Number", MonthNumber,

"Quarter", BaseDate,

"Month", FORMAT (BaseDate, "mmmm"),

"Year Month", FORMAT (BaseDate, "mmm yy")

))

[mahoneypat]

It looks like your Date, Day, and Quarter columns are DateTime instead of Date. I would change those data types.  Also, please see these two articles, that include M and/or DAX code to create Date tables that do not require Sort By Columns and a way to make a fiscal calendar that includes weeks.

No Sort Date Tables! – Hoosier BI

445 Calendar with 53-Week Years – Hoosier BI

Pat

[tlwest86]

Thank you so much for the resources!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[tlwest86]

https://drive.google.com/file/d/1rA1ergBhaftUlRD6YGx_kxJByhlIc0p7/view?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[tlwest86]

Thank you so much for your help.  What did you do to get this result?

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that as Answer.  I clicked on any one cell in the Month column of the Dates table and then clicked on Fiscal Year (refer first screenshot). 

[tlwest86]

Wow!  I was clicking on the column header and not on an actual cell.  Thanks again!

[Ashish_Mathur]

You are welcome.

[tlwest86]

No matter which column I sort by, the visulization does not change.  Since this has worked in the past, I recreated those reports with the same queries and still cannot get the months to display other than alphabetically. 

[tlwest86]

That did not work.

[amitchandak]

@tlwest86 , click on month and mark this column as sort column , You are marking it for itself

Example . selected column is month